Following my tutorial on how to extract meta informations from Firebird SQL, I'm now going to show how to retrieve the same informations from PostgreSQL, using the INFORMATION_SCHEMA (available since PostgreSQL 7.4) and with system catalogs (pg_class, pg_user, pg_view, etc).
NB: as you probably know, you can list tables/indices/sequences/views from the command line with the \d{t|i|s|v} command, but here I want to show how to extract these informations using standard SQL queries.

Test data

We need a few sample tables, indices and views to test the following queries, so let's create them. We also create a sample TRIGGER and a function.

List TABLEs

Here's the query that will return the names of the tables defined in the current database:

SELECT relname
FROM pg_class
WHERE relname !~ '^(pg_|sql_)'
AND relkind = 'r';
-- using INFORMATION_SCHEMA:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

List VIEWs

Here's the query that will return the names of the VIEWs defined in the current database:

-- with postgresql 7.2:
SELECT viewname
FROM pg_views
WHERE viewname !~ '^pg_';
-- with postgresql 7.4 and later:
SELECT viewname
FROM pg_views
WHERE schemaname NOT IN
('pg_catalog', 'information_schema')
AND viewname !~ '^pg_';
-- using INFORMATION_SCHEMA:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'VIEW'
AND table_schema NOT IN
('pg_catalog', 'information_schema')
AND table_name !~ '^pg_';
-- or
SELECT table_name
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name !~ '^pg_';

List users

SELECT usename
FROM pg_user;

List table fields

Here's the query that will return the names of the fields of the TEST2 table:

List INDICES

Here's the query that will return the names of the INDICES defined in the TEST2 table. Unfortunately I have no idea how to extract them from the INFORMATION_SCHEMA. If you do, please let me know.
NB: the CONSTRAINTs are not listed

SELECT relname
FROM pg_class
WHERE oid IN (
SELECT indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname='test2'
AND pg_class.oid=pg_index.indrelid
AND indisunique != 't'
AND indisprimary != 't'
);
-- Alternative using JOINs (thanks to William Stevenson):
SELECT
c.relname AS index_name
FROM
pg_class AS a
JOIN pg_index AS b ON (a.oid = b.indrelid)
JOIN pg_class AS c ON (c.oid = b.indexrelid)
WHERE
a.relname = 'test2';

Detailed INDEX info

If you want to know which table columns are referenced by an index, you can do it in two steps: first you get the table name and field(s) position with this query:

Detailed CONSTRAINT info

If you want to retrieve detailed info from any constraint (fields, type, rules, referenced table and fields for FOREIGN KEYs, etc.) given its name and table, here's the query to do so:

SELECT c.conname AS constraint_name,
CASE c.contype
WHEN 'c' THEN 'CHECK'
WHEN 'f' THEN 'FOREIGN KEY'
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE'
END AS "constraint_type",
CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,
CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,
t.relname AS table_name,
array_to_string(c.conkey, ' ') AS constraint_key,
CASE confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS on_update,
CASE confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS on_delete,
CASE confmatchtype
WHEN 'u' THEN 'UNSPECIFIED'
WHEN 'f' THEN 'FULL'
WHEN 'p' THEN 'PARTIAL'
END AS match_type,
t2.relname AS references_table,
array_to_string(c.confkey, ' ') AS fk_constraint_key
FROM pg_constraint c
LEFT JOIN pg_class t ON c.conrelid = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
WHERE t.relname = 'testconstraints2'
AND c.conname = 'testconstraints_id_fk';
-- with INFORMATION_SCHEMA:
SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE tc.table_name = 'testconstraints2'
AND tc.constraint_name = 'testconstraints_id_fk';

The "constraint_key" and "fk_constraint_key"
fields returned by the first query are space-separated strings containing the
position of the fields involved (in the FOREIGN KEY
constraint and those referenced by it), so you may need to retrieve them with
another query on the respective tables. Since the field positions are stored
as arrays, you can't (to the best of my knowledge) get all the field names
with an unique query (well, you could with a stored procedure).
The second query, the one using the INFORMATION_SCHEMA,
is certainly more straightforward, albeit slower.

List sequences

A SEQUENCE is an object that automatically generate sequence numbers.
A SEQUENCE is often used to ensure a unique value in a PRIMARY KEY that must uniquely identify the associated row.

SELECT relname
FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);

List TRIGGERs

SELECT trg.tgname AS trigger_name
FROM pg_trigger trg, pg_class tbl
WHERE trg.tgrelid = tbl.oid
AND tbl.relname !~ '^pg_';
-- or
SELECT tgname AS trigger_name
FROM pg_trigger
WHERE tgname !~ '^pg_';
-- with INFORMATION_SCHEMA:
SELECT DISTINCT trigger_name
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('pg_catalog', 'information_schema');

Detailed TRIGGER info

Show more informations about the trigger definitions:

SELECT trg.tgname AS trigger_name,
tbl.relname AS table_name,
p.proname AS function_name,
CASE trg.tgtype & cast(2 as int2)
WHEN 0 THEN 'AFTER'
ELSE 'BEFORE'
END AS trigger_type,
CASE trg.tgtype & cast(28 as int2)
WHEN 16 THEN 'UPDATE'
WHEN 8 THEN 'DELETE'
WHEN 4 THEN 'INSERT'
WHEN 20 THEN 'INSERT, UPDATE'
WHEN 28 THEN 'INSERT, UPDATE, DELETE'
WHEN 24 THEN 'UPDATE, DELETE'
WHEN 12 THEN 'INSERT, DELETE'
END AS trigger_event,
CASE trg.tgtype & cast(1 as int2)
WHEN 0 THEN 'STATEMENT'
ELSE 'ROW'
END AS action_orientation
FROM pg_trigger trg,
pg_class tbl,
pg_proc p
WHERE trg.tgrelid = tbl.oid
AND trg.tgfoid = p.oid
AND tbl.relname !~ '^pg_';
-- with INFORMATION_SCHEMA:
SELECT *
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('pg_catalog', 'information_schema');

List FUNCTIONs

SELECT proname
FROM pg_proc pr,
pg_type tp
WHERE tp.oid = pr.prorettype
AND pr.proisagg = FALSE
AND tp.typname <> 'trigger'
AND pr.pronamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
-- with INFORMATION_SCHEMA:
SELECT routine_name
FROM information_schema.routines
WHERE specific_schema NOT IN
('pg_catalog', 'information_schema')
AND type_udt_name != 'trigger';

Albe Laurenz sent me the following function that is even more informative: for a function name and schema, it selects the position in the argument list, the direction, the name and the data-type of each argument.
This procedure requires PostgreSQL 8.1 or later.

Show PROCEDURE definition

SELECT p.proname AS procedure_name,
p.pronargs AS num_args,
t1.typname AS return_type,
a.rolname AS procedure_owner,
l.lanname AS language_type,
p.proargtypes AS argument_types_oids,
prosrc AS body
FROM pg_proc p
LEFT JOIN pg_type t1 ON p.prorettype=t1.oid
LEFT JOIN pg_authid a ON p.proowner=a.oid
LEFT JOIN pg_language l ON p.prolang=l.oid
WHERE proname = :PROCEDURE_NAME;

pg_proc.proargtypes contains an array of oids pointing to pg_type.oid. You can use unnest(), generate_procedure() or the function in the previous paragraph to retrieve the data type of each parameter.

What else?

If you'd like to see some other example queries, or have some comments and/or suggestions, just drop me a mail (you can find my address in the footer of this page) and I'll add them to this list.
HTH.

hi,
reading through your queries, really good stuff. One slight but critical part is missing and I am wondering if you could add it.
Under the section 'Detailed CONSTRAINT info' you have 2 queries that retrieve index information, I am interested in getting 4 columns for foreign keys, src_table, src_column, dst_table, dst_column, your 2nd information_schema sql can do this fine but it is slowww, your longer query above that one is much faster but is missing just one column, the dst_column (foreign table column name) and I have tried but cant find how to reliable retrieve it as I dont have an ERD for the pg_* tables.
Any help is much appreciated, thanks!

Hi, what you want is a not easy with one single query, because to get the name of the fields you have to do a JOIN on pg_attribute, but the pg_constraint conkey and confkey fields are arrays (listing the column numbers within the tables in the constraint). If you know your FOREIGN KEY constraint is on a single field, you can get what you want with this query:

If the FK is on multiple columns, then either you parse the conkey/confkeys arrays and do another query to select the names of the matching columns, or you try something like generate_subscripts(): http://www.postgresql.org/docs/8.4/static/arrays.html

Thank you so much for this information. I am building a postgresql module for my open source statistics package SOFA Statistics and I found it very hard to get the required information in a useful form.

Lorenzo hi!
Thank you for sharing your knowledge with us. I found your notes very useful.
Have you got anything similar concerning how to retrieve users queries on a postgres database?
I 've tried pg_stat_activity but it only shows current queries and only select queries (and not update, insert, delete).
I 've tried pg_log directory by changing in the conf file the log_line_prefix and log_statement= 'all' but it has implications on the load (creates extreme big log file).
Is there any easy way to track users queries?
Thank you
Pepy

Hi, I am new to PostgreSQL. I have started learning and written triggers and they are working fine. Currently I am using spi_exe_query to insert/update my log table when primary table updated. Could you please give me an example of spi_prepare and spi_execute_plan usage in PL/Perl that I can use in my trigger. I couldn\'t find any example in documentation.
Basically, I want to prepare a plan and want to store it in %_SHARED hash so that I can use whenever I want. Moreover, if table definition changed then I should purge the cache.
Any suggestion would be much appreciated.
Thanks, Jignesh

Jordi, the first query you mention only lists constraints, not indices. The second one is not entirely correct either, since it will list all the columns having some constraint. A better way of listing the PRIMARY KEYs is:

Jignesh, have a look at my article about
table auditing: http://www.alberton.info/postgresql_table_audit.html. It's not something I would recommend on a live system, but it's OK if you only want to play around.

i'm getting some strange results for both information schema queries for detailed pk's and detailed constraint's. looks almost like a cartesian product.
how portable is information_schema, i hear its a standard but is this like browser standards or real standards?

re: my last comment, i neglected to notice or mention that for detailed constraint info i left off the constraint name in where clause.
if i modify the above PK query to look for tc.constraint_type = 'FOREIGN KEY' i get odd results as well.
any idea how to query the information schema to get full FK details on a given table without specifying the constraint name?

Thanks Lorenzo. You have an excellent resource for us here. In spite of all this I'm unable to solve my problem. Can you please help with this. I need to query column statistics to get tablename, rowcount, columnname, no. of nulls, null percentage and no. of distinct values.
Thanks for your help.

Hi,I am new to PostgreSql.I found your material very helpful.I am trying to write a generic function for auditing databases using pl/pgsql.I tried to use the same logic as you have used in your function using pl/tcl (http://www.alberton.info/postgresql_table_audit.html) but the use of new and old is giving errors.Is it not possible to convert new and old into arrays and then use them in pl/pgsql?If yes,then can you please help me out in this?
Thank You

Do you know how to extract more information about the sequences in Postgres ??
Basically I need the sequence name (which I got ), its start value, increment value and max value (I tried the information_schema.sequences but dont see any inofrmation except names)

@Sameer Unfortunately I don't know. The INFORMATION_SCHEMA.SEQUENCE view is incomplete (as you can see from the definition [1], it's returning NULL for those values), probably waiting for the relevant info to be exposed. Looking at the sequence.c file [2], it looks like it shouldn't be that hard to add a function returning the sequence info.
[1] http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/catalog/information_schema.sql;hb=HEAD
[2] http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/commands/sequence.c;hb=HEAD

In regards to constraint meta data. I've been attempting to extract information about constraints as well, mostly in regard to foreign keys. I started with a query from stack overflow that I found ( http://stackoverflow.com/questions/1567051/introspect-postgresql-8-3-to-find-foreign-keys ) and modified it somewhat. This isn't quite what I need, but it is a start, and maybe it will help others too. What I'm interested in doing with this, eventually, is also including field counts from tables with those foreign references. I'm still simply trying learn about the pg_catalogs and meta data and how to best extract meaningful information from them. Here's a query that gives the four column output mentioned in the conversation about constraints, albeit, only for foreign keys. I didn't write the original version, and I've changed it a little bit, possibly for the worse, but I've learned a lot toying with it. Any help or comments or corrections would be appreciated.

This page has been an incredible resource. I have it bookmarked and I been using it for two weeks as a reference.
I noticed that you use information_schema in many places. This is incredibly trivial compared to the system table. However, for completeness, I noticed you didn\'t have the information_schema for enabled_rules (instead of pg_user);
select * from information_schema.enabled_roles ;

Lorenzo,
WOW! Thank you so much for putting this together! The sample database you have at the top is great, as is the rest of the stuff you have here!
I do have one question. I am trying to gather index information and I see that you have a two step query for that. I would like to know how to determine whether the index is unique or not and the position of the fields within the index. Can that be done?
THANKS!!!!

I need to retrieve a functions "CONTEXT". Basically, I want to retrieve what functions called a sub-function. I know this information is available because it displays in the pgsql log file. Great resource you have here.

Hi Lorenzo, is there a query to display the actual trigger create statement:
(CREATE TRIGGER some_trigger BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE some_function();) ,
or we have to construct it based on the information from this query "SELECT * FROM information_schema.triggers WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema');"
Thanks

If I have one table Foo with column row_id that is being referenced by table FooBar.foo_id and also another table FooBee.foo_id, could you give an example on how to find if row_id X in Foo is being referenced by another table?
Thanks

This page has been very helpful to me several times in the past several years in which I've been using postgres. I'm grateful to the author.
Several times I've used this information to figure out how to grant permissions to all objects in a schema to a particular user.
I thought it might be helpful to mention that, as of 9.0, postgres does have the syntax to grant privileges on all tables (as well as other objects) in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;

Hello, I find this article very useful... I am working on query optimization in PostgreSql. Can you tell me how to make changes in PostgreSql Metadata.How should I go about it as I am making small changes in the queries...
Plz reply asap.
Regards,
Aditi

Change all references to "unique_constraint_*" to "constraint_*" in your joins. The way it is now, it will only work for unique constraints. By removing the "unique_" prefix, it should work for all constrains, including non-unique constraints. I'm using it for foreign keys without the "unique_"; prefix, and it seems to be working fine.

Hi Lorenzo, we are migrating our application from oracle DB to Postgres DB. in which one of piece of code requires metadata. So i am converting all oracle queries to Postgres. But here i am stucked can you please help me in this regard. The Oracle query is