Getting table metadata

Look at the SQL in the PG Admin III project source code base.

http://www.pgadmin.org/

It's non-trivial SQL to collect all the information about a table.

________________________________

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Tozier
Sent: Tuesday, January 18, 2005 9:15 PM
To: PostgreSQL
Subject: [GENERAL] Getting table metadata

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&quot; 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