acytoo

Database Postgresql Query Metadata

Display all the information schema views supported by PostgreSQL and which we can use for retrieving database meta information for current database

SELECT * 
FROM information_schema.views 
WHERE table_schema = 'information_schema' 
ORDER BY table_name;

To retrieve name of the current database, this will list all tables within database, both user defined and postgresql defined. Basically all information we need is stored in those table, try to select * from them and see.

SELECT * 
FROM information_schema.information_schema_catalog_name;

To retrieve a list of tables and views defined in the current database

SELECT table_catalog, table_schema, table_name, table_type 
FROM information_schema.tables 
ORDER BY table_name;

To retrieve a list of user defined tables defined in the current database

SELECT table_catalog, table_schema, table_name, table_type 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
AND table_schema <> 'pg_catalog' 
AND table_schema <> 'information_schema' 
ORDER BY table_name;

To list the schemas owned by currently logged in user

SELECT * 
FROM information_schema.schemata 
ORDER BY schema_name;

To list all the views in the current database for which the currently logged in user has access permissions

SELECT * 
FROM information_schema.views;

To list all timestamp columns in your schema

SELECT * 
FROM information_schema.columns 
WHERE table_schema = 'public' /* public is the target schema name */
AND data_type = 'timestamp without time zone';

To show all foreign keys in the PostgreSQL database

SELECT conrelid::regclass AS table_name, 
       conname AS foreign_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'f' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

List all tables sorted in decreasing order by the number of tuples they contain.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS tuple_number
FROM tbl
ORDER BY tuple_number DESC;

List all stored procedures

SELECT n.nspname AS schema,
       p.proname AS procedure
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND p.prokind = 'p';

Check procedure queries and edit

\ef [procedure_name]

See Also: