In an earlier post we looked at the Queries for Sql Server for retrieving meta information. In this post, we will look at the PostgresSql version.
Get All Views
SELECT table_name, view_definition FROM information_schema.VIEWS WHERE table_schema = ANY ( Current_schemas(FALSE) );
Get All Tables
SELECT table_name FROM information_schema.TABLES WHERE table_schema = ANY ( Current_schemas(FALSE) )
Get All Columns
SELECT column_name, is_nullable, is_identity, character_maximum_length, numeric_precision, numeric_precision_radix, numeric_scale, data_type FROM information_schema.COLUMNS WHERE table_schema = 'public' AND table_name = '<TableName>'
Get All Indices from database
SELECT ix.indisunique AS IsUnique, t.relname AS TableName, i.relname AS Name, a.attname AS ColumnName FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY ( ix.indkey ) AND t.relkind = 'r' AND ix.indisprimary = FALSE AND t.relname = '<TableName>';
Get All Primary Key Constraints
SELECT kcu.table_schema, tco.constraint_name, kcu.column_name FROM information_schema.table_constraints tco join information_schema.key_column_usage kcu ON kcu.constraint_name = tco.constraint_name AND kcu.constraint_schema = tco.constraint_schema AND kcu.constraint_name = tco.constraint_name WHERE tco.constraint_type = 'PRIMARY KEY' AND kcu.table_name = '<TableName>' ORDER BY kcu.table_schema, kcu.table_name
Get all Unique Key Constraints
SELECT kcu.table_schema, tco.constraint_name, kcu.column_name FROM information_schema.table_constraints tco join information_schema.key_column_usage kcu ON kcu.constraint_name = tco.constraint_name AND kcu.constraint_schema = tco.constraint_schema AND kcu.constraint_name = tco.constraint_name WHERE tco.constraint_type = 'UNIQUE' AND kcu.table_name = '<TableName>' ORDER BY kcu.table_schema, kcu.table_name;
Get all check constraints
SELECT col.column_name AS ColumnName, tc.constraint_name AS Name, cc.check_clause AS DEFINITION FROM information_schema.table_constraints tc join information_schema.check_constraints cc ON tc.constraint_schema = cc.constraint_schema AND tc.constraint_name = cc.constraint_name join pg_namespace nsp ON nsp.nspname = cc.constraint_schema join pg_constraint pgc ON pgc.conname = cc.constraint_name AND pgc.connamespace = nsp.oid AND pgc.contype = 'c' join information_schema.COLUMNS col ON col.table_schema = tc.table_schema AND col.table_name = tc.table_name AND col.ordinal_position = ANY ( pgc.conkey ) WHERE tc.constraint_schema NOT IN( 'pg_catalog', 'information_schema' ) ORDER BY tc.table_schema, tc.table_name;
Get all Foreign Key Constraints
SELECT tc.constraint_name, kcu.column_name, ccu.table_name, ccu.column_name FROM information_schema.table_constraints AS tc join information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = '<TableName>'
Get all Default value Constraints
SELECT col.column_name AS ColumnName, col.column_default AS DefaultValue FROM information_schema.COLUMNS col WHERE col.column_default IS NOT NULL AND col.table_schema NOT IN( 'information_schema', 'pg_catalog' ) AND col.table_name = '<TableName>' ORDER BY col.column_name;
WordPress.com
