pg_catalog Queries
pg_catalog Queries is a SQL statement in the PostgreSQL Specific category. PostgreSQL: system catalog tables that provide metadata about the database (tables, columns, indexes, etc.). The syntax is SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public';. It returns system catalog info. A typical example: -- List all tables with row counts: SELECT relname AS table_name, n_live_tup AS estimated_rows FROM pg_stat_user_tables ORDER BY n_live_tup DESC; -- Find tables with no primary key: SELECT tab.table_schema, tab.table_name FROM information_schema.tables tab LEFT JOIN information_schema.table_constraints tc ON tab.table_name = tc.table_name AND tc.constraint_type = 'PRIMARY KEY' WHERE tab.table_type = 'BASE TABLE' AND tc.table_name IS NULL; -- List all indexes: SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'; A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).