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]