Thread: Getting table metadata

<fontfamily><param>Arial</param>I recently stumbled upon the system
catalog functions here
"http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that
it's easy to get a list of all databases and relatively easy to get a
list of tables, but there doesn't seem to be any built in method for
retrieving a table definition. The best I could come up with would be
to do a select something like this:


SELECT * FROM pg_attribute WHERE attrelid=<<the table oid>;


The problem I'm running into however, is that given a table name,
there doesn't seem to be any way to get the table oid. Is there some
function or query that does this? Better yet, is there an easier way
to get at this metadata?


Thanks for any help,


Ken</fontfamily>
I recently stumbled upon the system catalog functions here
"http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that
it's easy to get a list of all databases and relatively easy to get a
list of tables, but there doesn't seem to be any built in method for
retrieving a table definition. The best I could come up with would be
to do a select something like this:

SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;

The problem I'm running into however, is that given a table name, there
doesn't seem to be any way to get the table oid. Is there some function
or query that does this? Better yet, is there an easier way to get at
this metadata?

Thanks for any help,

Ken

On Wed, Jan 19, 2005 at 12:14:57AM -0500, Ken Tozier wrote:

> I recently stumbled upon the system catalog functions here
> "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that
> it's easy to get a list of all databases and relatively easy to get a
> list of tables, but there doesn't seem to be any built in method for
> retrieving a table definition.

See also "The Information Schema" if you're using 7.4 or later.

> The best I could come up with would be to do a select something
> like this:
>
> SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;
>
> The problem I'm running into however, is that given a table name, there
> doesn't seem to be any way to get the table oid.

See "Object Identifier Types" in the "Data Types" chapter.

SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass;

> Is there some function or query that does this? Better yet, is there
> an easier way to get at this metadata?

If you run "psql -E" you can see the queries that psql makes when
you issue commands like "\d tablename".  As you can see, it takes
a lot of information from the system catalogs to generate a description
of a table.  The Information Schema abstracts these queries through
views, so querying them might be the easiest way if they provide
what you need.  See in particular information_schema.columns.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael,

> See "Object Identifier Types" in the "Data Types" chapter.
>
> SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass;

Thanks. That worked like a champ!

Ken

>
> SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;
>
> The problem I'm running into however, is that given a table name, there
> doesn't seem to be any way to get the table oid. Is there some function
> or query that does this?

I think a way to get the table oid is:

select oid from pg_class where relname=<table_name>

Ntinos Katsaros

I am not the original poster.

Here is what PSQL does to get the table name list:
connxdatasync=# \d
********* QUERY *********
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as
"Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'v'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'v'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
  AND c.relname !~ '^pg_'
ORDER BY "Name"
*************************

Here is the sort of queries that would be made by PSQL to collect
information about a single table:

connxdatasync=# \d "LastActions"
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='LastActions'
*************************

********* QUERY *********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'LastActions'
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'LastActions' AND c.oid = i.indrelid AND i.indexrelid
= c2.oid
ORDER BY c2.relname
*************************

-----Original Message-----
From: ntinos@aueb.gr [mailto:ntinos@aueb.gr]
Sent: Wednesday, January 19, 2005 12:29 AM
To: Dann Corbit
Cc: Ken Tozier; PostgreSQL
Subject: Re: Getting table metadata

>
> SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;
>
> The problem I'm running into however, is that given a table name,
there
> doesn't seem to be any way to get the table oid. Is there some
function
> or query that does this?

I think a way to get the table oid is:

select oid from pg_class where relname=<table_name>

Ntinos Katsaros