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:
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:
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_catalogorinformation_schemarelation 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.viewsis 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.