PostgreSQL System Catalog



PostgreSQL-compatible TiDB Cloud Starter provides pg_catalog relations and information_schema views for inspecting databases, schemas, tables, columns, indexes, constraints, functions, roles, and other database objects.

pg_catalog relations

The following commonly used pg_catalog relations are available:

RelationDescription
pg_tablesUser tables.
pg_viewsViews.
pg_classTables, indexes, sequences, and views.
pg_attributeTable columns.
pg_attrdefColumn default values.
pg_namespaceSchemas.
pg_typeData types.
pg_indexIndex metadata.
pg_indexesIndex definitions, including indexdef.
pg_constraintPrimary key, foreign key, check, unique, and not-null constraints.
pg_procFunctions and procedures.
pg_triggerTrigger metadata.
pg_enumEnum values.
pg_sequenceSequence metadata.
pg_extensionExtensions registered in the database.
pg_collationBuilt-in collations.
pg_rolesDatabase roles.
pg_userLogin users.
pg_auth_membersRole membership.
pg_db_role_settingPer-role session parameter defaults.
pg_databaseDatabases.
pg_descriptionObject comments and descriptions.
pg_dependObject dependency metadata.
pg_amIndex access methods.
pg_stat_user_tablesUser-table statistics.
pg_inheritsTable inheritance metadata.
pg_rangeRange type metadata.
pg_opclassOperator classes.
pg_policyRow-level security policy metadata.
pg_policiesReadable row-level security policy definitions.
pg_settingsRuntime parameters.
pg_timezone_namesTime zone names and UTC offsets.
pg_rewriteRewrite rules used by views.
pg_aggregateAggregate function metadata.

Some catalog relations exist primarily for PostgreSQL compatibility even when the corresponding PostgreSQL feature is not supported. Do not treat the presence of a catalog relation as proof that the underlying PostgreSQL feature is available.

For example, logical replication is not supported even though some replication-related catalog objects can be present.

information_schema views

The following information_schema views are available:

ViewDescription
tablesTables and views.
columnsTable columns, including generated-column metadata.
schemataSchemas.
sequencesSequences.
routinesFunctions and procedures.
table_constraintsPrimary key, foreign key, check, and unique constraints.
key_column_usageColumns in primary-key, unique, and foreign-key constraints.
referential_constraintsForeign-key relationships.
check_constraintsCheck constraints.
constraint_column_usageColumns referenced by constraints.
table_privilegesTable privileges.
triggersTrigger definitions, timing, and orientation.

For column nullability, use information_schema.columns.is_nullable or pg_attribute.attnotnull.

List tables in a schema

Use pg_tables:

SELECT tablename AS table, tableowner AS owner, hasindexes AS indexed, hastriggers AS has_triggers FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

For a more portable query, use information_schema.tables:

SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name;

List columns of a table

Use information_schema.columns:

SELECT column_name, data_type, character_maximum_length, is_nullable, column_default, ordinal_position FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'my_table' ORDER BY ordinal_position;

For lower-level metadata, query pg_attribute:

SELECT a.attname AS column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS not_null, a.attnum AS position FROM pg_attribute AS a JOIN pg_class AS c ON c.oid = a.attrelid JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relname = 'my_table' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;

Find indexes on a table

Use pg_indexes:

SELECT indexname AS index_name, indexdef AS definition FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'my_table' ORDER BY indexname;

For more detailed index metadata:

SELECT i.relname AS index_name, ix.indisunique AS is_unique, ix.indisprimary AS is_primary FROM pg_index AS ix JOIN pg_class AS t ON t.oid = ix.indrelid JOIN pg_class AS i ON i.oid = ix.indexrelid JOIN pg_namespace AS n ON n.oid = t.relnamespace WHERE n.nspname = 'public' AND t.relname = 'my_table' ORDER BY i.relname;

Check table constraints

Use information_schema for common constraints:

SELECT tc.constraint_name, tc.constraint_type, kcu.column_name, cc.check_clause FROM information_schema.table_constraints AS tc LEFT JOIN information_schema.key_column_usage AS kcu ON kcu.constraint_name = tc.constraint_name AND kcu.constraint_schema = tc.constraint_schema AND kcu.table_schema = tc.table_schema AND kcu.table_name = tc.table_name LEFT JOIN information_schema.check_constraints AS cc ON cc.constraint_name = tc.constraint_name AND cc.constraint_schema = tc.constraint_schema WHERE tc.table_schema = 'public' AND tc.table_name = 'my_table' ORDER BY tc.constraint_type, tc.constraint_name;

Use pg_constraint for a concise definition:

SELECT conname AS constraint_name, contype AS constraint_type, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE conrelid = 'public.my_table'::regclass ORDER BY contype, conname;

pg_constraint also exposes not-null constraints. To check column nullability in a PostgreSQL-portable way, prefer information_schema.columns.is_nullable.

Find foreign keys

Use information_schema to inspect outbound foreign keys:

SELECT tc.constraint_name, kcu.column_name AS fk_column, referenced_kcu.table_name AS referenced_table, referenced_kcu.column_name AS referenced_column, rc.update_rule, rc.delete_rule FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON kcu.constraint_name = tc.constraint_name AND kcu.constraint_schema = tc.constraint_schema AND kcu.table_schema = tc.table_schema AND kcu.table_name = tc.table_name JOIN information_schema.referential_constraints AS rc ON rc.constraint_name = tc.constraint_name AND rc.constraint_schema = tc.constraint_schema JOIN information_schema.key_column_usage AS referenced_kcu ON referenced_kcu.constraint_name = rc.unique_constraint_name AND referenced_kcu.constraint_schema = rc.unique_constraint_schema AND referenced_kcu.ordinal_position = kcu.position_in_unique_constraint WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public' AND tc.table_name = 'my_table' ORDER BY tc.constraint_name;

List functions and procedures

Use information_schema.routines:

SELECT routine_name, routine_type, data_type AS return_type FROM information_schema.routines WHERE routine_schema = 'public' ORDER BY routine_name;

For additional metadata, query pg_proc:

SELECT p.proname AS function_name, format_type(p.prorettype, NULL) AS return_type, p.prokind AS kind FROM pg_proc AS p JOIN pg_namespace AS n ON n.oid = p.pronamespace WHERE n.nspname = 'public' ORDER BY p.proname;

Inspect roles and privileges

List roles:

SELECT rolname, rolcanlogin, rolinherit, rolbypassrls FROM pg_roles ORDER BY rolname;

List table privileges:

SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_schema = 'public' ORDER BY table_name, grantee, privilege_type;

Inspect row-level security

List policies for a table:

SELECT policyname AS policy, cmd AS command, roles AS applies_to, qual AS using_expression, with_check AS check_expression FROM pg_policies WHERE schemaname = 'public' AND tablename = 'my_table' ORDER BY policyname;

Check whether row-level security is enabled or forced:

SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE oid = 'public.my_table'::regclass;

Inspect extensions

List extensions registered in the current database:

SELECT extname, extversion FROM pg_extension ORDER BY extname;

System catalog compatibility

PostgreSQL-compatible TiDB Cloud Starter implements the catalog surface needed by common PostgreSQL tools and introspection workflows, but catalog contents and columns can differ from upstream PostgreSQL.

In particular:

  • Not every upstream pg_catalog or information_schema relation is available.
  • Some catalog relations exist for compatibility even when the underlying PostgreSQL feature is unsupported.
  • Some catalog relations expose a subset of upstream PostgreSQL columns.
  • information_schema.views is not available.
  • Custom collations can be used but are not listed in pg_collation.
  • Trigger timing and row/statement orientation should be read from information_schema.triggers.

Was this page helpful?