ℹ

PostgreSQL System Information Functions

Complete reference for PostgreSQL system information functions covering database version, current user and role, session settings, object sizes, and privilege testing. Includes pg_database_size(), pg_table_size(), current_setting(), and has_table_privilege(). Updated for PostgreSQL 16.

53 functions

Jump to function

col_description current_database current_schema current_user format_type has_any_column_privilege has_column_privilege has_database_privilege has_foreign_data_wrapper_privilege has_function_privilege has_language_privilege has_schema_privilege has_sequence_privilege has_server_privilege has_table_privilege has_tablespace_privilege has_type_privilege information_schema_catalog_name obj_description pg_backend_pid pg_control_checkpoint pg_control_init pg_control_recovery pg_control_system pg_current_xact_id pg_current_xact_id_if_assigned pg_database_size pg_describe_object pg_get_constraintdef pg_get_expr pg_get_function_arguments pg_get_function_identity_arguments pg_get_function_result pg_get_functiondef pg_get_indexdef pg_get_ruledef pg_get_serial_sequence pg_get_triggerdef pg_get_viewdef pg_has_role pg_identify_object pg_input_is_valid pg_postmaster_start_time pg_relation_size pg_size_pretty pg_total_relation_size pg_typeof pg_xact_commit_timestamp pg_xact_commit_timestamp_only session_user txid_current txid_current_if_assigned version

What are PostgreSQL System Information Functions?

PostgreSQL system information functions return metadata about the database server, current session, user roles, and object sizes. They are essential for database monitoring, permission auditing, and storage capacity planning. Key system information functions include version(), current_user, pg_database_size(), pg_table_size(), pg_relation_size(), current_setting(), and has_column_privilege().

col_description

PG 7.2+→ text

Returns the comment stored for a table column. Column comments are set with COMMENT ON COLUMN. The column is identified by its attribute number (attnum), not its name.

DBADeveloper

Signature

col_description ( table oid, column integer ) → text

Parameters

Parameter	Type	Description
table	oid	OID of the table
column	integer	Attribute number of the column (pg_attribute.attnum)

Examples

Comment on first column of orders

sql

SELECT col_description('orders'::regclass, 1);

→Primary key identifier

All column comments for customers table

sql

SELECT attname, col_description('customers'::regclass, attnum) FROM pg_attribute WHERE attrelid='customers'::regclass AND attnum > 0 AND NOT attisdropped;

→(rows)

Set then retrieve column comment

sql

COMMENT ON COLUMN orders.amount IS 'Order total in EUR'; SELECT col_description('orders'::regclass, attnum) FROM pg_attribute WHERE attrelid='orders'::regclass AND attname='amount';

→Order total in EUR

Column comments via information_schema join

sql

SELECT column_name, col_description(table_oid::regclass, ordinal_position) FROM information_schema.columns WHERE table_name = 'products';

→(rows)

Full column documentation for API reference

sql

SELECT attname, col_description(attrelid, attnum) AS doc FROM pg_attribute WHERE attrelid = 'api_keys'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum;

→(rows)

⚠Anti-Pattern— Using column names instead of attnum with col_description

col_description() requires the attribute number (attnum), not the column name. Using the wrong number (e.g. 0 or a name string) always returns NULL.

✓ Instead: Join with pg_attribute to get attnum by name: SELECT col_description('t'::regclass, attnum) FROM pg_attribute WHERE attrelid='t'::regclass AND attname='mycol';

Store column descriptions as COMMENT ON COLUMN and use col_description() to generate OpenAPI schema descriptions directly from PostgreSQL — one source of truth for field documentation.

example

SELECT json_build_object(attname, json_build_object('description', col_description(attrelid, attnum), 'type', format_type(atttypid,atttypmod))) FROM pg_attribute WHERE attrelid='users'::regclass AND attnum>0 AND NOT attisdropped;

→JSON object suitable for OpenAPI schema generation

current_database

PG 7.4+→ name

Returns the name of the currently connected database.

DeveloperDBA

Signature

current_database ( ) → name

Examples

sql

SELECT current_database();

→myapp_production

Full session context snapshot for diagnostics

sql

SELECT current_database(), current_schema(), current_user, pg_is_in_recovery() AS is_standby;

→'myapp' | 'public' | 'appuser' | false

Build a connection string from session context

sql

SELECT 'postgresql://' || current_user || '@localhost/' || current_database() AS connection_string;

→'postgresql://appuser@localhost/myapp'

Verify current database allows new connections

sql

SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname = current_database() AND datallowconn) AS can_connect;

→true

⚠Anti-Pattern— Hardcoding the database name in cross-database queries

Hardcoding the database name in application code or queries breaks when deploying to different environments (dev/staging/prod) that use different database names.

✓ Instead: Use current_database() wherever the database name is needed dynamically. In connection strings, reference it from the connection context rather than hardcoding.

Use `current_database()` in migration scripts or utility queries instead of hardcoding the database name. This makes scripts reusable across development, staging, and production.

example

SELECT 'ALTER DATABASE ' || current_database() || ' SET timezone TO UTC;';

→Dynamic SQL for the current database

current_schema

PG 7.4+→ name (or name[])

Returns the name of the current schema (first in the search path). current_schemas returns all schemas in the search path.

DeveloperDBA

Signatures

current_schema ( ) → name

current_schemas ( boolean ) → name[]

Parameters

Parameter	Type	Description
include_implicit	boolean	For current_schemas: if true, include implicitly searched schemas like pg_catalog

Examples

sql

SELECT current_schema();

→public

sql

SELECT current_schemas(false);

→{public}

sql

SELECT current_schemas(true);

→{pg_catalog,public}

current_schemas(true) includes implicit schemas like pg_catalog

sql

SELECT current_schema(), current_schemas(true) AS full_path;

→'public' | '{pg_catalog,public}'

⚠Anti-Pattern— Using current_schema() as a constant in schema-qualified DDL

current_schema() returns the first schema in search_path that exists. It can change between sessions if search_path is set differently. DDL should use explicit schema names, not current_schema().

✓ Instead: Use explicit schema names in CREATE TABLE/INDEX/VIEW statements. Use current_schema() only for diagnostic queries, not for schema-qualified object creation.

When a table isn't found or the wrong table is used, print `current_schemas(true)` to see the full search path including implicit schemas. Change it with `SET search_path = myschema, public`.

example

SELECT current_schema(), current_schemas(true);

→Current schema and full search path

current_user

PG 7.4+→ name

Returns the user name of the current execution context (the role whose privilege context is currently active).

DeveloperSecurity

Signature

current_user → name

Examples

sql

SELECT current_user;

→alice

sql

SELECT current_user = session_user;

→false if SET ROLE was used

When equal, not inside a SECURITY DEFINER function

sql

SELECT current_user, session_user, current_user = session_user AS not_inside_security_definer;

→app_user | app_user | true

Filter audit records for the current effective user

sql

SELECT count(*) FROM audit_log WHERE performed_by = current_user AND created_at > now() - interval '1 day';

→42

⚠Anti-Pattern— Using session_user instead of current_user for RLS policies

session_user is the original login role. SECURITY DEFINER functions run as their owner — inside them, current_user is the owner, not the caller. For RLS policies, use current_user to get the effective user.

✓ Instead: In RLS policies: USING (owner_id = current_user) captures the effective role. Use session_user only when you need the original login identity specifically.

`session_user` is the original login user. `current_user` can differ if `SET ROLE` was used. In Row Level Security policies, use `current_user` for role-based checks and `session_user` for auditing who actually connected.

example

CREATE POLICY user_isolation ON data USING (owner = current_user);

→RLS policy: users only see their own rows

Common Gotchas

⚠

Sequences don't roll back — gaps appear after any aborted transaction

SERIAL/SEQUENCE values are consumed even if the transaction rolls back. You'll see gaps in IDs — this is expected and correct behavior.

format_type

PG 7.4+→ text

Returns the SQL type name of a data type identified by OID and optional type modifier (e.g. length for varchar, precision/scale for numeric). Returns NULL if inputs are NULL.

DBADeveloper

Signature

format_type ( type oid, typemod integer ) → text

Parameters

Parameter	Type	Description
type	oid	OID from pg_type.oid
typemod	integer	Type modifier (e.g. -1 for no modifier, or the packed modifier from pg_attribute.atttypmod)

Examples

OID 23 = integer, no modifier

sql

SELECT format_type(23, -1);

→integer

OID 1043 = varchar, modifier 14 = length 10

sql

SELECT format_type(1043, 14);

→character varying(10)

Column names and types for a table

sql

SELECT attname, format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'my_table'::regclass AND attnum > 0 AND NOT attisdropped;

→(rows)

Exact type with precision and scale

sql

SELECT format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'orders'::regclass AND attname = 'amount';

→numeric(10,2)

Function return types in readable form

sql

SELECT proname, format_type(prorettype, -1) AS return_type FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

→(rows)

⚠Anti-Pattern— Reading type names directly from pg_type without modifier

SELECT typname FROM pg_type loses the type modifier — varchar without length, numeric without precision. atttypmod encodes the modifier and must be passed to format_type() to get the full type.

✓ Instead: Always use format_type(atttypid, atttypmod) from pg_attribute for accurate full column type strings.

format_type(atttypid, atttypmod) is the standard way to get column type exactly as it appears in CREATE TABLE — including precision, scale, and array dimensions.

example

SELECT attname AS column, format_type(atttypid,atttypmod) AS type, attnotnull AS not_null FROM pg_attribute WHERE attrelid='orders'::regclass AND attnum>0 AND NOT attisdropped ORDER BY attnum;

→Complete column type information matching information_schema.columns

has_any_column_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user if omitted) has the specified privilege on at least one column of the given table. Privilege types: SELECT, INSERT, UPDATE, REFERENCES. Returns true if even a single column grants the privilege.

DBASecurity

Signatures

has_any_column_privilege ( user name, table text, privilege text ) → boolean

has_any_column_privilege ( user oid, table oid, privilege text ) → boolean

has_any_column_privilege ( table text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to test (defaults to current_user)
table	text or oid	Table name or OID
privilege	text	Privilege to check: SELECT, INSERT, UPDATE, or REFERENCES

Examples

sql

SELECT has_any_column_privilege('app_user', 'employees', 'SELECT');

→true — app_user can SELECT at least one column of employees

sql

SELECT has_any_column_privilege('reports', 'SELECT');

→true if current user has SELECT on any column of the reports table

sql

SELECT has_any_column_privilege('analyst', 'salary_data', 'SELECT');

→false — analyst has no SELECT on any column of salary_data

sql

SELECT table_name FROM information_schema.tables WHERE has_any_column_privilege(current_user, table_name, 'SELECT') AND table_schema = 'public';

→All tables in public schema where current user can read at least one column

sql

SELECT has_any_column_privilege('app_user', 'orders', 'UPDATE');

→true if app_user may update at least one column in orders

⚠Anti-Pattern— Using has_any_column_privilege as a substitute for row-level security

Column-level GRANTs restrict which columns a user can query but do not filter which rows they see. A user with SELECT on a single non-sensitive column can still issue `SELECT non_sensitive_col FROM employees` and see all rows. Use Row-Level Security (RLS) policies to restrict row visibility.

✓ Instead: ALTER TABLE employees ENABLE ROW LEVEL SECURITY; CREATE POLICY emp_view ON employees FOR SELECT USING (dept_id = current_setting('app.dept_id')::int);

`has_table_privilege` checks table-wide privileges. `has_any_column_privilege` checks column-level GRANTs — useful when you use column-level security to restrict sensitive fields like salary or SSN while still allowing access to the rest of the row.

example

SELECT has_table_privilege('analyst', 'employees', 'SELECT') AS table_access, has_any_column_privilege('analyst', 'employees', 'SELECT') AS col_access;

→table_access: false | col_access: true — column-level grant only

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_column_privilege

PG 8.4+→ boolean

Tests whether a user has a specific privilege on a table column. Privilege types: SELECT, INSERT, UPDATE, REFERENCES. Column-level privileges can restrict or extend table-level grants.

DBA

Signatures

has_column_privilege ( user name/oid, table text/oid, column text/smallint, privilege text ) → boolean

has_column_privilege ( table text/oid, column text/smallint, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user
table	text or oid	Table name or OID
column	text or smallint	Column name or attribute number
privilege	text	SELECT, INSERT, UPDATE, or REFERENCES

Examples

Analyst cannot see salary column

sql

SELECT has_column_privilege('analyst', 'employees', 'salary', 'SELECT');

→false

Current user can read name column

sql

SELECT has_column_privilege('employees', 'name', 'SELECT');

→true

Column-level SELECT audit for reporter user

sql

SELECT attname, has_column_privilege('reporter', 'customers', attname, 'SELECT') FROM pg_attribute WHERE attrelid='customers'::regclass AND attnum > 0 AND NOT attisdropped;

→(rows)

data_entry cannot update amounts

sql

SELECT has_column_privilege('data_entry', 'orders', 'total_amount', 'UPDATE');

→false

Even current user may lack access to sensitive column

sql

SELECT has_column_privilege(current_user, 'users', 'password_hash', 'SELECT');

→false

⚠Anti-Pattern— Using table-level SELECT and assuming all columns are accessible

GRANT SELECT ON TABLE t grants access to all current columns including future ones. If new sensitive columns are added, the grant automatically extends to them.

✓ Instead: For tables with sensitive columns, use column-level grants or use RLS (Row Level Security) with column masking views.

Grant SELECT on all non-sensitive columns at table level, then explicitly deny (by not granting) SELECT on sensitive columns like SSN, salary, or password_hash. Column-level grants are additive.

example

GRANT SELECT(id, name, email) ON customers TO analyst_role; -- salary column not included

→Analyst can query id/name/email but not salary

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_database_privilege

PG 7.4+→ boolean

Tests whether a user has a specific privilege on a database. Privilege types: CREATE, CONNECT, TEMPORARY (or TEMP). CONNECT is required to connect at all.

DBA

Signatures

has_database_privilege ( user name/oid, database text/oid, privilege text ) → boolean

has_database_privilege ( database text/oid, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user
database	text or oid	Database name or OID
privilege	text	CREATE, CONNECT, or TEMPORARY/TEMP

Examples

Check connection privilege on current DB

sql

SELECT has_database_privilege('app_user', current_database(), 'CONNECT');

→true

Current user: can create temp tables?

sql

SELECT has_database_privilege('reporting', 'TEMPORARY');

→false

Can developer create schemas in this DB?

sql

SELECT has_database_privilege('developer', current_database(), 'CREATE');

→true

Which databases can current user connect to?

sql

SELECT datname, has_database_privilege(current_user, datname, 'CONNECT') FROM pg_database;

→(rows)

Verify readonly user cannot create schemas

sql

SELECT has_database_privilege('readonly_user', 'prod_db', 'CREATE');

→false

⚠Anti-Pattern— Assuming CONNECT means full access

CONNECT only allows connecting to the database. Schema USAGE and table privileges are separately required for actual data access. A user can have CONNECT but be unable to read any data.

✓ Instead: Always check the full privilege chain: has_database_privilege(CONNECT) → has_schema_privilege(USAGE) → has_table_privilege(SELECT).

Before revoking CONNECT from a database (e.g. REVOKE CONNECT ON DATABASE prod FROM PUBLIC), audit which roles still have it explicitly granted to avoid locking out application users.

example

SELECT rolname, has_database_privilege(rolname, 'prod_db', 'CONNECT') FROM pg_roles WHERE rolcanlogin ORDER BY rolname;

→All login roles with their CONNECT status on prod_db

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_foreign_data_wrapper_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a foreign data wrapper. The only applicable privilege is USAGE, which is required to create foreign servers using that wrapper.

DBASecurity

Signatures

has_foreign_data_wrapper_privilege ( user name, fdw text, privilege text ) → boolean

has_foreign_data_wrapper_privilege ( user oid, fdw oid, privilege text ) → boolean

has_foreign_data_wrapper_privilege ( fdw text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
fdw	text or oid	Foreign data wrapper name or OID
privilege	text	Must be 'USAGE'

Examples

sql

SELECT has_foreign_data_wrapper_privilege('app_user', 'postgres_fdw', 'USAGE');

→true if app_user can use postgres_fdw

sql

SELECT has_foreign_data_wrapper_privilege('postgres_fdw', 'USAGE');

→true if current user has USAGE on postgres_fdw

sql

SELECT fdwname FROM pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege(fdwname, 'USAGE');

→All FDWs accessible to the current user

sql

SELECT has_foreign_data_wrapper_privilege('readonly_role', 'file_fdw', 'USAGE');

→false — readonly_role has not been granted USAGE on file_fdw

⚠Anti-Pattern— Checking FDW privilege without also checking server privilege

USAGE on the foreign data wrapper is a prerequisite but not sufficient — the user also needs USAGE on the foreign server and a user mapping. Checking only `has_foreign_data_wrapper_privilege` gives an incomplete picture of whether a user can actually query a foreign table.

✓ Instead: SELECT has_foreign_data_wrapper_privilege('u', 'postgres_fdw', 'USAGE') AND has_server_privilege('u', 'remote_db', 'USAGE') AS can_query_foreign;

A user needs USAGE on both the foreign data wrapper AND the foreign server to create user mappings and query foreign tables. Granting server USAGE alone is not sufficient if the FDW USAGE is missing.

example

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO app_user;
GRANT USAGE ON FOREIGN SERVER remote_db TO app_user;

→User can now create user mappings and query foreign tables

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_function_privilege

PG 7.4+→ boolean

Tests whether a user has EXECUTE privilege on a function or procedure. By default, PUBLIC has EXECUTE on most built-in functions. Only EXECUTE privilege applies to functions.

DBA

Signatures

has_function_privilege ( user name/oid, function text/oid, privilege text ) → boolean

has_function_privilege ( function text/oid, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user
function	text or oid	Function specified by name with argument types, or OID
privilege	text	Must be EXECUTE

Examples

Check EXECUTE on function with signature

sql

SELECT has_function_privilege('app_user', 'my_proc(integer)', 'EXECUTE');

→true

Current user cannot execute sensitive function

sql

SELECT has_function_privilege('sensitive_func()', 'EXECUTE');

→false

Audit EXECUTE on all functions in schema

sql

SELECT p.proname, has_function_privilege('analyst', p.oid, 'EXECUTE') FROM pg_proc p JOIN pg_namespace n ON p.pronamespace=n.oid WHERE n.nspname='myapp';

→(rows)

Non-admin user cannot call admin function

sql

SELECT has_function_privilege(current_user, 'admin_function(text)', 'EXECUTE');

→false

Functions not accessible to PUBLIC role

sql

SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace AND NOT has_function_privilege('PUBLIC', oid, 'EXECUTE');

→(rows)

⚠Anti-Pattern— Forgetting to restrict SECURITY DEFINER functions

SECURITY DEFINER functions run with the owner's privileges. If PUBLIC can execute them, any user can indirectly use the owner's elevated privileges.

✓ Instead: After creating SECURITY DEFINER functions: REVOKE EXECUTE ON FUNCTION myfunc FROM PUBLIC; GRANT EXECUTE ON FUNCTION myfunc TO trusted_role;

By default, all users (via PUBLIC) can execute functions you create. For functions that access sensitive data or perform privileged operations, explicitly revoke and re-grant: REVOKE EXECUTE ON FUNCTION myfunc FROM PUBLIC; GRANT EXECUTE ON FUNCTION myfunc TO privileged_role;

example

SELECT proname FROM pg_proc WHERE pronamespace='public'::regnamespace AND has_function_privilege('PUBLIC', oid, 'EXECUTE') AND prosecdef;

→Security-definer functions accessible to PUBLIC (potential risk)

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_language_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a procedural language. The only applicable privilege is USAGE, which is required to create functions in that language.

DBASecurity

Signatures

has_language_privilege ( user name, language text, privilege text ) → boolean

has_language_privilege ( user oid, language oid, privilege text ) → boolean

has_language_privilege ( language text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
language	text or oid	Language name or OID (e.g., 'plpgsql', 'plpython3u')
privilege	text	Must be 'USAGE'

Examples

sql

SELECT has_language_privilege('plpgsql', 'USAGE');

→true if current user can write PL/pgSQL functions

sql

SELECT has_language_privilege('dev_role', 'plpython3u', 'USAGE');

→false — plpython3u is untrusted; only superusers have USAGE by default

sql

SELECT lanname FROM pg_language WHERE has_language_privilege(lanname, 'USAGE');

→All procedural languages accessible to the current user

sql

SELECT has_language_privilege('app_owner', 'plpgsql', 'USAGE');

→true — plpgsql is trusted and PUBLIC has USAGE by default

sql

SELECT lanname, lanpltrusted FROM pg_language WHERE NOT has_language_privilege(current_user, lanname, 'USAGE');

→Untrusted languages the current user cannot use

⚠Anti-Pattern— Granting USAGE on untrusted languages to non-superusers

Untrusted languages (e.g., plpython3u) can execute arbitrary OS-level code. Granting USAGE on them to non-superuser roles is a serious security risk — it effectively gives those users unrestricted server access.

✓ Instead: Keep untrusted language functions in a dedicated superuser-owned schema. Use trusted language wrappers (PL/pgSQL) to expose limited functionality to application roles.

Trusted languages (plpgsql, plperl, pltcl) have PUBLIC USAGE granted by default. Untrusted variants (plpython3u, plperlu) require superuser or an explicit GRANT. Check `pg_language.lanpltrusted` to distinguish them.

example

SELECT lanname, lanpltrusted, has_language_privilege(lanname, 'USAGE') AS accessible FROM pg_language;

→plpgsql: trusted=true, accessible=true | plpython3u: trusted=false, accessible=false

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_schema_privilege

PG 7.4+→ boolean

Tests whether a user has a specific privilege on a schema. Privilege types: CREATE, USAGE. USAGE allows seeing objects; CREATE allows creating objects in the schema.

DBA

Signatures

has_schema_privilege ( user name/oid, schema text/oid, privilege text ) → boolean

has_schema_privilege ( schema text/oid, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user
schema	text or oid	Schema name or OID
privilege	text	CREATE or USAGE

Examples

Check if analyst can see objects in reporting schema

sql

SELECT has_schema_privilege('analyst', 'reporting', 'USAGE');

→true

Current user USAGE on public schema

sql

SELECT has_schema_privilege('public', 'USAGE');

→true

app_user cannot create in internal schema

sql

SELECT has_schema_privilege('app_user', 'internal', 'CREATE');

→false

USAGE audit across all non-system schemas

sql

SELECT nspname, has_schema_privilege('myapp', nspname, 'USAGE') FROM pg_namespace WHERE nspname NOT LIKE 'pg_%';

→(rows)

Can current user create in current schema?

sql

SELECT has_schema_privilege(current_user, current_schema(), 'CREATE');

→true

⚠Anti-Pattern— Granting table privileges without schema USAGE

Granting SELECT on a table without GRANT USAGE ON SCHEMA results in users still getting "permission denied for schema" errors.

✓ Instead: Always pair table grants with schema USAGE: GRANT USAGE ON SCHEMA myschema TO myuser; GRANT SELECT ON TABLE myschema.mytable TO myuser;

A user needs USAGE on a schema AND SELECT/INSERT/etc. on a table to actually access it. Schema USAGE alone is not enough. Always check both when diagnosing access issues.

example

SELECT has_schema_privilege('u','myschema','USAGE') AND has_table_privilege('u','myschema.mytable','SELECT') AS can_access;

→True only if both schema and table access are granted

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_sequence_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a sequence. Applicable privileges: USAGE (to call nextval/currval), SELECT (to call currval/lastval), UPDATE (to call nextval/setval).

DBASecurity

Signatures

has_sequence_privilege ( user name, sequence text, privilege text ) → boolean

has_sequence_privilege ( user oid, sequence oid, privilege text ) → boolean

has_sequence_privilege ( sequence text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
sequence	text or oid	Sequence name or OID
privilege	text	USAGE, SELECT, or UPDATE

Examples

sql

SELECT has_sequence_privilege('order_id_seq', 'USAGE');

→true if current user can call nextval on this sequence

sql

SELECT has_sequence_privilege('app_user', 'users_id_seq', 'UPDATE');

→true if app_user can call setval on users_id_seq

sql

SELECT sequence_name FROM information_schema.sequences WHERE has_sequence_privilege(sequence_name, 'USAGE') AND sequence_schema = 'public';

→All sequences the current user can increment

sql

SELECT has_sequence_privilege('reporting', 'events_id_seq', 'SELECT');

→true if reporting role can read the current sequence value

sql

SELECT has_sequence_privilege('app_user', 'order_id_seq', 'USAGE, UPDATE');

→true if app_user has both USAGE and UPDATE on the sequence

⚠Anti-Pattern— Granting table INSERT without sequence USAGE breaks serial columns

A role granted INSERT on a table backed by a SERIAL or BIGSERIAL column will fail with 'permission denied for sequence' unless it also has USAGE on the sequence. `GRANT INSERT ON table TO role` does not automatically propagate to the sequence.

✓ Instead: GRANT USAGE ON SEQUENCE users_id_seq TO app_user; -- Or for all sequences in schema: GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Application roles that INSERT rows typically need USAGE on the sequence backing the primary key (even if the sequence is owned by the table). Use `has_sequence_privilege` in your privilege audit queries to verify this is correctly granted.

example

SELECT relname AS table, s.relname AS seq, has_sequence_privilege('app_user', s.oid, 'USAGE') AS has_usage FROM pg_class t JOIN pg_depend d ON d.refobjid = t.oid JOIN pg_class s ON s.oid = d.objid WHERE s.relkind = 'S';

→Privilege audit for all sequences backing table primary keys

Common Gotchas

⚠

Sequences don't roll back — gaps appear after any aborted transaction

SERIAL/SEQUENCE values are consumed even if the transaction rolls back. You'll see gaps in IDs — this is expected and correct behavior.

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_server_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a foreign server. The only applicable privilege is USAGE, which allows the user to create user mappings and query foreign tables through that server.

DBASecurity

Signatures

has_server_privilege ( user name, server text, privilege text ) → boolean

has_server_privilege ( user oid, server oid, privilege text ) → boolean

has_server_privilege ( server text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
server	text or oid	Foreign server name or OID
privilege	text	Must be 'USAGE'

Examples

sql

SELECT has_server_privilege('app_user', 'analytics_server', 'USAGE');

→true if app_user can use the analytics_server foreign server

sql

SELECT has_server_privilege('remote_pg', 'USAGE');

→true if current user has USAGE on remote_pg foreign server

sql

SELECT srvname FROM pg_foreign_server WHERE has_server_privilege(srvname, 'USAGE');

→All foreign servers accessible to current user

sql

SELECT has_server_privilege('readonly_role', 'data_warehouse', 'USAGE');

→false — readonly_role cannot access data_warehouse foreign server

⚠Anti-Pattern— Not revoking server USAGE when a user's access is terminated

When offboarding a user who had foreign table access, revoking schema and table privileges is not sufficient — the foreign server USAGE and user mapping must also be dropped. Otherwise the user's credentials remain in pg_user_mappings and may still be usable in some configurations.

✓ Instead: DROP USER MAPPING IF EXISTS FOR ex_user SERVER remote_db; REVOKE USAGE ON FOREIGN SERVER remote_db FROM ex_user;

A user must have USAGE on both the foreign data wrapper AND the specific foreign server. Check both using `has_foreign_data_wrapper_privilege` and `has_server_privilege` together in privilege audits.

example

SELECT has_foreign_data_wrapper_privilege('u', 'postgres_fdw', 'USAGE') AS fdw_ok, has_server_privilege('u', 'remote_db', 'USAGE') AS srv_ok;

→Both must be true for the user to query foreign tables

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_table_privilege

PG 7.4+→ boolean

Tests whether a user has a specific privilege on a table. Privilege types: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER. Returns true/false/NULL (NULL if user or table does not exist).

DBA

Signatures

has_table_privilege ( user name/oid, table text/oid, privilege text ) → boolean

has_table_privilege ( table text/oid, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user if omitted
table	text or oid	Table name (optionally schema-qualified) or OID
privilege	text	Privilege to check: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER

Examples

Check if myuser can SELECT from orders

sql

SELECT has_table_privilege('myuser', 'orders', 'SELECT');

→true

Check current_user INSERT privilege on orders

sql

SELECT has_table_privilege('orders', 'INSERT');

→false

Multiple privileges: all must hold

sql

SELECT has_table_privilege('reporting_user', 'sensitive_data', 'SELECT, INSERT');

→false

Audit SELECT access across all public tables

sql

SELECT tablename, has_table_privilege('analyst', tablename, 'SELECT') FROM pg_tables WHERE schemaname = 'public';

→(rows)

Explicit current_user check

sql

SELECT has_table_privilege(current_user, 'audit_log', 'DELETE');

→false

⚠Anti-Pattern— Using pg_table_is_visible() to check access

pg_table_is_visible() checks if a table is in the search_path — not whether the current user has any privilege on it. A visible table can still be inaccessible.

✓ Instead: Use has_table_privilege(current_user, table_name, privilege) for actual access checking.

Join has_table_privilege() with pg_tables and pg_roles to produce a full privilege matrix for a schema — useful for access reviews and compliance audits.

example

SELECT r.rolname, t.tablename, has_table_privilege(r.rolname, t.tablename, 'SELECT') AS can_select FROM pg_roles r CROSS JOIN pg_tables t WHERE t.schemaname='public' ORDER BY 1,2;

→Full SELECT privilege matrix for public schema

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_tablespace_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a tablespace. The only applicable privilege is CREATE, which allows creating tables and indexes in that tablespace.

DBA

Signatures

has_tablespace_privilege ( user name, tablespace text, privilege text ) → boolean

has_tablespace_privilege ( user oid, tablespace oid, privilege text ) → boolean

has_tablespace_privilege ( tablespace text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
tablespace	text or oid	Tablespace name or OID
privilege	text	Must be 'CREATE'

Examples

sql

SELECT has_tablespace_privilege('fast_storage', 'CREATE');

→true if current user can create objects in the fast_storage tablespace

sql

SELECT has_tablespace_privilege('app_owner', 'ssd_tbs', 'CREATE');

→true if app_owner can use ssd_tbs tablespace

sql

SELECT spcname FROM pg_tablespace WHERE has_tablespace_privilege(spcname, 'CREATE');

→All tablespaces available to the current user

sql

SELECT has_tablespace_privilege('analyst', 'pg_default', 'CREATE');

→true — pg_default is accessible to all users

⚠Anti-Pattern— Using default tablespace without checking user privilege

A table created without an explicit TABLESPACE clause lands in the role's default tablespace (or the database default). If migration scripts assume pg_default but the database has a custom default tablespace, objects may end up in unexpected locations.

✓ Instead: Always specify TABLESPACE explicitly in DDL scripts and verify the role has CREATE privilege first: `SELECT has_tablespace_privilege(current_user, 'target_tbs', 'CREATE');`

When placing a table in a non-default tablespace via `CREATE TABLE ... TABLESPACE ssd_tbs`, the creating role needs CREATE privilege on that tablespace. Verify with `has_tablespace_privilege` before running migrations that place objects in specific tablespaces.

example

SELECT has_tablespace_privilege('app_owner', 'ssd_tbs', 'CREATE') AS can_place_tables;

→true — safe to run CREATE TABLE ... TABLESPACE ssd_tbs

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

has_type_privilege

PG 9.1+→ boolean

Tests whether a user (or the current user) has the specified privilege on a data type. The only applicable privilege is USAGE, which is required to use a custom type in column definitions, casts, or function signatures.

DBASecurity

Signatures

has_type_privilege ( user name, type text, privilege text ) → boolean

has_type_privilege ( user oid, type oid, privilege text ) → boolean

has_type_privilege ( type text, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	Role to check (defaults to current_user)
type	text or oid	Type name or OID (can be schema-qualified)
privilege	text	Must be 'USAGE'

Examples

sql

SELECT has_type_privilege('myapp.order_status', 'USAGE');

→true if current user can use the order_status enum type

sql

SELECT has_type_privilege('app_user', 'public.currency_code', 'USAGE');

→true if app_user can reference the currency_code domain type

sql

SELECT typname FROM pg_type WHERE typtype IN ('e','d','c') AND has_type_privilege(oid, 'USAGE');

→All custom enum, domain, and composite types accessible to current user

sql

SELECT has_type_privilege('reporting', 'hstore', 'USAGE');

→true if reporting role can use the hstore extension type

⚠Anti-Pattern— Forgetting to GRANT USAGE on custom types when setting up roles

Schema-level CREATE and USAGE grants do not propagate to types defined in that schema. After running `GRANT USAGE ON SCHEMA myapp TO app_user`, the user still cannot reference custom types unless each type also has `GRANT USAGE ON TYPE myapp.order_status TO app_user`.

✓ Instead: -- In PG 14+, grant on all existing types in a schema at once: GRANT USAGE ON ALL TYPES IN SCHEMA public TO app_user; -- Or use ALTER DEFAULT PRIVILEGES for future types.

When you create a custom enum, domain, or composite type, only the owner has USAGE by default. Application roles need an explicit GRANT. This is easy to miss in migration scripts — use `has_type_privilege` to audit type accessibility.

example

SELECT typname FROM pg_type WHERE typtype = 'e' AND NOT has_type_privilege('app_user', oid, 'USAGE');

→Enum types not yet accessible to app_user

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

information_schema_catalog_name

PG 9.3+→ information_schema.sql_identifier (character varying(64))

A single-row view in information_schema that returns the name of the current database as a sql_identifier. Part of the SQL standard information_schema. Use current_database() for a simpler equivalent.

Developer

Signature

SELECT catalog_name FROM information_schema.information_schema_catalog_name

Examples

sql

SELECT catalog_name FROM information_schema.information_schema_catalog_name;

→myapp — current database name

sql

SELECT catalog_name = current_database() AS matches FROM information_schema.information_schema_catalog_name;

→true — both return the current database name

sql

SELECT * FROM information_schema.information_schema_catalog_name;

→catalog_name: 'production'

sql

-- Standard SQL equivalent of:
SELECT current_database();

→production

⚠Anti-Pattern— Joining to information_schema_catalog_name in performance-sensitive queries

The information_schema is a layer of views on top of pg_catalog. While these views are fine for metadata queries, querying through them in performance-sensitive code (e.g., inside a PL/pgSQL loop) adds overhead. Use `current_database()` directly instead.

✓ Instead: IF current_database() = 'prod' THEN ... -- In PL/pgSQL

In PostgreSQL-specific code, `current_database()` is cleaner and more efficient. Use `information_schema.information_schema_catalog_name` only when you need SQL-standard portability across different database engines.

example

SELECT current_database(); -- simpler and equivalent

→myapp

obj_description

PG 7.2+→ text

Returns the comment stored for a database object (e.g. table, column, function, type). Comments are set with the COMMENT ON command. Returns NULL if no comment exists.

DBADeveloper

Signatures

obj_description ( object oid, catalog name ) → text

obj_description ( object oid ) → text

Parameters

Parameter	Type	Description
object	oid	OID of the object
catalog	name	System catalog name, e.g. 'pg_class' for tables/views, 'pg_proc' for functions, 'pg_type' for types

Examples

Get table comment

sql

SELECT obj_description('orders'::regclass, 'pg_class');

→Customer purchase orders

Get function comment

sql

SELECT obj_description('my_function'::regproc, 'pg_proc');

→Calculates discount rate

All table comments in public schema

sql

SELECT relname, obj_description(oid, 'pg_class') AS comment FROM pg_class WHERE relnamespace = 'public'::regnamespace AND relkind = 'r';

→(rows)

Set then retrieve a comment

sql

COMMENT ON TABLE orders IS 'Customer purchase orders'; SELECT obj_description('orders'::regclass, 'pg_class');

→Customer purchase orders

Comments on custom types

sql

SELECT typname, obj_description(oid, 'pg_type') FROM pg_type WHERE typnamespace = 'public'::regnamespace;

→(rows)

⚠Anti-Pattern— Querying pg_description directly for object comments

SELECT description FROM pg_description WHERE objoid = ... requires knowing the correct classoid and objsubid. obj_description() handles the catalog join automatically.

✓ Instead: Use obj_description(oid, catalog) for cleaner, safer comment retrieval without manual pg_description joins.

Pair obj_description() with information_schema or pg_class/pg_proc to generate live schema documentation directly from the database, ensuring docs stay in sync with the schema.

example

SELECT table_name, obj_description(('public.'||table_name)::regclass, 'pg_class') AS description FROM information_schema.tables WHERE table_schema = 'public';

→Table names with their COMMENT ON descriptions

pg_backend_pid

PG 9.1+→ integer

Returns the process ID of the server process handling the current session.

DBAReliability

Signature

pg_backend_pid ( ) → integer

Examples

sql

SELECT pg_backend_pid();

→1234 (varies)

sql

SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();

→Info about the current session

Globally unique session identifier

sql

SELECT pg_backend_pid(), pg_postmaster_start_time() AS instance_start, concat(pg_backend_pid()::text, '-', extract(epoch FROM pg_postmaster_start_time())::bigint) AS unique_session_id;

→12345 | 2026-03-16 08:00:00 | 12345-1742112000

Inspect own session in pg_stat_activity

sql

SELECT query, state, wait_event_type FROM pg_stat_activity WHERE pid = pg_backend_pid();

→(current query details)

⚠Anti-Pattern— Using pg_backend_pid() as a unique session identifier across restarts

PIDs are reused by the OS after a process exits. pg_backend_pid() uniquely identifies a backend within a running instance, but the same PID may be reused after a restart or reconnect.

✓ Instead: For session tracking that survives reconnects, use a combination of pg_backend_pid() + pg_postmaster_start_time() as a composite key, or use application-level session UUIDs.

Use `pg_backend_pid()` to query `pg_stat_activity` for your own session's information. Useful for debugging lock waits or query state in application code.

example

SELECT state, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = pg_backend_pid();

→Current session's activity state and wait events

pg_control_checkpoint

PG 12+ (as a SQL function)→ record (checkpoint_lsn pg_lsn, redo_lsn pg_lsn, redo_wal_file text, timeline_id integer, prev_timeline_id integer, full_page_writes boolean, next_xid text, next_oid oid, next_multixact_id oid, next_multi_offset oid, oldest_xid oid, oldest_xid_dbid oid, oldest_active_xid oid, oldest_multi_xid oid, oldest_multi_dbid oid, oldest_commit_ts_xid oid, newest_commit_ts_xid oid, checkpoint_time timestamp with time zone)

Returns checkpoint information from the pg_control file, including the latest checkpoint LSN, redo LSN, timeline ID, next OID, next multixact ID, and whether the cluster was shut down cleanly.

DBA

Signature

pg_control_checkpoint ( ) → record

Examples

sql

SELECT checkpoint_lsn, redo_lsn, timeline_id FROM pg_control_checkpoint();

→checkpoint_lsn: 0/4A2B3C4D | redo_lsn: 0/4A2B1234 | timeline_id: 1

sql

SELECT checkpoint_time FROM pg_control_checkpoint();

→2025-03-16 09:45:22+00 — time of the last checkpoint

sql

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), checkpoint_lsn)) AS wal_since_checkpoint FROM pg_control_checkpoint();

→1432 kB — WAL generated since last checkpoint

sql

SELECT next_oid FROM pg_control_checkpoint();

→24601 — next OID to be assigned

⚠Anti-Pattern— Parsing pg_controldata CLI output in scripts instead of querying pg_control_checkpoint()

Before PG12, `pg_controldata` was a CLI-only tool whose output format is not stable across versions. Scripts that `grep` its output break on version upgrades. Use `pg_control_checkpoint()` for all script-based monitoring on PG12+.

✓ Instead: SELECT checkpoint_lsn, redo_lsn, checkpoint_time FROM pg_control_checkpoint(); -- Stable SQL interface

Use `pg_wal_lsn_diff(pg_current_wal_lsn(), checkpoint_lsn)` to measure how much WAL has been generated since the last checkpoint. Large values indicate checkpoint_completion_target or checkpoint_segments may need tuning.

example

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), checkpoint_lsn)) AS lag FROM pg_control_checkpoint();

→23 MB — WAL since last checkpoint

Common Gotchas

⚠

Unlogged tables are wiped on crash or unclean shutdown

UNLOGGED tables skip WAL and are extremely fast, but ALL data is truncated on crash or unclean shutdown — silently.

pg_control_init

PG 12+ (as a SQL function)→ record (max_data_alignment integer, database_block_size integer, blocks_per_segment integer, wal_block_size integer, bytes_per_wal_segment integer, max_identifier_length integer, max_index_columns integer, max_toast_chunk_size integer, large_object_chunk_size integer, float8_pass_by_value boolean, data_page_checksum_version integer)

Returns initialization parameters from the pg_control file — values set at initdb time and fixed for the life of the cluster: maximum data alignment, database block size, WAL block size, maximum identifier length, and whether the server uses integer datetimes.

DBA

Signature

pg_control_init ( ) → record

Examples

sql

SELECT database_block_size, wal_block_size FROM pg_control_init();

→database_block_size: 8192 | wal_block_size: 8192 (standard 8KB blocks)

sql

SELECT max_identifier_length FROM pg_control_init();

→63 — maximum bytes in a PostgreSQL identifier

sql

SELECT bytes_per_wal_segment FROM pg_control_init();

→16777216 — 16 MB per WAL segment (default)

sql

SELECT data_page_checksum_version FROM pg_control_init();

→1 if checksums are enabled, 0 if not

⚠Anti-Pattern— Assuming block size is always 8192 without checking

PostgreSQL can be compiled with non-standard block sizes (4KB, 16KB, 32KB). Code or monitoring queries that hard-code 8192 will produce wrong results on clusters compiled with a different `--with-blocksize`. Always read `database_block_size` from `pg_control_init()` dynamically.

✓ Instead: SELECT database_block_size FROM pg_control_init(); -- Don't assume 8192

Data page checksums protect against silent storage corruption. Use `pg_control_init()` to confirm `data_page_checksum_version = 1` (enabled). On PG12+, you can also enable checksums online with `pg_checksums --enable` on a stopped server.

example

SELECT data_page_checksum_version = 1 AS checksums_enabled FROM pg_control_init();

→true — data checksums are active

Common Gotchas

⚠

Unlogged tables are wiped on crash or unclean shutdown

UNLOGGED tables skip WAL and are extremely fast, but ALL data is truncated on crash or unclean shutdown — silently.

pg_control_recovery

PG 12+ (as a SQL function)→ record (min_recovery_end_lsn pg_lsn, min_recovery_end_timeline integer, backup_start_lsn pg_lsn, backup_end_lsn pg_lsn, end_of_backup_record_required boolean)

Returns recovery-related information from the pg_control file: the minimum recovery end LSN, the required WAL LSN at that point, the minimum recovery end timeline, and whether the backup end position is set.

DBA

Signature

pg_control_recovery ( ) → record

Examples

sql

SELECT * FROM pg_control_recovery();

→

min_recovery_end_lsn: 0/3C1A2B3D | min_recovery_end_timeline: 1 | backup_start_lsn: 0/0 | backup_end_lsn: 0/0 | end_of_backup_record_required: false

sql

SELECT min_recovery_end_lsn FROM pg_control_recovery();

→0/3C1A2B3D — standby cannot promote below this LSN

sql

SELECT end_of_backup_record_required FROM pg_control_recovery();

→true — cluster started from a base backup and must see the backup end record before accepting connections

sql

SELECT backup_start_lsn, backup_end_lsn FROM pg_control_recovery();

→Both 0/0 on a running primary (no pending base backup restore)

⚠Anti-Pattern— Ignoring end_of_backup_record_required during base backup restores

When restoring from a base backup, `end_of_backup_record_required` is true until the WAL recovery process has read the backup end marker. Starting the server in normal mode before recovery completes can leave the cluster in an inconsistent state.

✓ Instead: Always allow WAL recovery to complete naturally. Monitor `end_of_backup_record_required` going to false before considering the restore finished.

On a standby, `min_recovery_end_lsn` shows the earliest LSN at which it can safely promote. If `pg_current_wal_receive_lsn()` has not yet reached `min_recovery_end_lsn`, promotion will be blocked. Monitor both values in failover scripts.

example

SELECT min_recovery_end_lsn, pg_is_in_recovery() FROM pg_control_recovery();

→Shows recovery progress and minimum promotion threshold

pg_control_system

PG 12+ (as a SQL function; previously only available via pg_controldata CLI)→ record (pg_control_version integer, catalog_version_no integer, system_identifier bigint, pg_control_last_modified timestamp with time zone)

Returns information from the pg_control file: pg_control version number, catalog version number, system identifier (unique cluster ID), and the pg_control last modified timestamp.

DBA

Signature

pg_control_system ( ) → record

Examples

sql

SELECT * FROM pg_control_system();

→

pg_control_version: 1300, catalog_version_no: 202307071, system_identifier: 7234567890123456789, pg_control_last_modified: 2025-03-16 08:00:00+00

sql

SELECT system_identifier FROM pg_control_system();

→7234567890123456789 — unique cluster ID, same across primary and standbys

sql

SELECT catalog_version_no FROM pg_control_system();

→202307071 — catalog format version, changes with major upgrades

sql

SELECT pg_control_last_modified FROM pg_control_system();

→2025-03-16 08:00:00+00 — when pg_control was last updated (e.g., last checkpoint)

⚠Anti-Pattern— Using pg_control_system to check PostgreSQL version instead of server_version_num

`pg_control_version` in `pg_control_system()` is the control file format version (e.g., 1300 for PG13), NOT the PostgreSQL server version. Using it for version comparisons in application code will give wrong results.

✓ Instead: SELECT current_setting('server_version_num')::integer; -- 130000 for PG13, 160000 for PG16

The `system_identifier` is generated once when the cluster is initialised with `initdb`. It is identical on all standbys of the same primary. Compare it across servers to verify they belong to the same cluster — a mismatch means the standby was initialised from a different base backup.

example

SELECT system_identifier FROM pg_control_system(); -- Run on primary and all standbys; values must match

→7234567890123456789 (same on all nodes of the cluster)

pg_current_xact_id

PG 13+→ xid8

Returns the current transaction's ID as an xid8 (64-bit transaction ID). Assigns a new transaction ID if none has been assigned. Introduced in PostgreSQL 13 as the modern replacement for txid_current().

DeveloperDBA

Signature

pg_current_xact_id ( ) → xid8

Examples

sql

SELECT pg_current_xact_id();

→1234567 (as xid8 type)

sql

SELECT pg_current_xact_id()::text AS xid_str;

→'1234567'

sql

SELECT pg_current_xact_id() AS xid, current_timestamp AS ts INTO TEMP TABLE checkpoint_info;

→Saves current XID and timestamp for later comparison

sql

SELECT pg_visible_in_snapshot(xmin, pg_current_snapshot()) AS was_visible FROM big_table WHERE id = 42;

→true if the row's inserting transaction is visible in the current snapshot

⚠Anti-Pattern— Storing xid8 values as bigint for cross-version compatibility

Casting `pg_current_xact_id()::bigint` to store in a bigint column loses the type semantics. The xid8 type participates in `pg_visible_in_snapshot` and related functions. If you store as bigint, you cannot use those functions without casting back.

✓ Instead: Store as `xid8` natively: `ALTER TABLE audit ADD COLUMN xact_id xid8;` — it occupies 8 bytes, same as bigint.

Unlike the 32-bit xid (which wraps after ~2 billion transactions), xid8 is a 64-bit epoch-aware transaction ID that will not wrap in any practical timeframe. Use it when storing transaction IDs for audit trails or change tracking.

example

ALTER TABLE audit_log ADD COLUMN xact_id xid8 DEFAULT pg_current_xact_id();

→Audit rows stamped with the current transaction's 64-bit ID

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

⚠

pg_dump is consistent but still impacts production — it holds a snapshot

pg_dump takes a consistent snapshot, but that snapshot prevents autovacuum from removing dead tuples — causing table bloat during long backups.

pg_current_xact_id_if_assigned

PG 13+→ xid8

Returns the current transaction's ID as xid8 only if one has been assigned (i.e., a write has occurred). Returns NULL for read-only transactions. Introduced in PostgreSQL 13 as the modern replacement for txid_current_if_assigned().

DeveloperDBA

Signature

pg_current_xact_id_if_assigned ( ) → xid8

Examples

sql

SELECT pg_current_xact_id_if_assigned();

→NULL — current transaction is read-only

sql

BEGIN; INSERT INTO events(msg) VALUES ('test'); SELECT pg_current_xact_id_if_assigned();

→1234570 (as xid8) — XID assigned when write occurred

sql

SELECT COALESCE(pg_current_xact_id_if_assigned()::text, 'read-only') AS xact_id;

→'read-only' for SELECT-only transactions

sql

-- Use in audit trigger to avoid XID waste in read paths:
SELECT pg_current_xact_id_if_assigned() IS NOT NULL AS has_writes;

→false for read-only transactions

⚠Anti-Pattern— Using pg_current_xact_id() in SELECT-heavy background workers

Background workers or health-check queries that call `pg_current_xact_id()` for logging purposes unnecessarily consume transaction IDs from the XID space. Use `pg_current_xact_id_if_assigned()` and skip recording when it returns NULL.

✓ Instead: IF pg_current_xact_id_if_assigned() IS NOT NULL THEN -- record xid; END IF;

In triggers or hooks that want to record the transaction ID, use `pg_current_xact_id_if_assigned()` rather than `pg_current_xact_id()`. The latter always assigns an XID even in read-only contexts. Since triggers fire on writes anyway, both are equivalent there — but the pattern avoids confusion in test code.

example

CREATE TRIGGER set_xact BEFORE INSERT ON audit FOR EACH ROW SET NEW.xact_id = pg_current_xact_id_if_assigned();

→xact_id populated with write-transaction ID

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

pg_database_size

PG 8.1+→ bigint

Returns the total disk space used by the specified database.

DBAReliability

Signatures

pg_database_size ( name ) → bigint

pg_database_size ( oid ) → bigint

Parameters

Parameter	Type	Description
database_name	name or oid	Database name or OID

Examples

sql

SELECT pg_size_pretty(pg_database_size(current_database()));

→156 MB

sql

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

→All databases with their sizes

All databases ranked by size

sql

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;

→

myapp | 4096 MB
test | 128 MB

Total PostgreSQL storage across all databases

sql

SELECT pg_size_pretty(sum(pg_database_size(datname))) AS total_pg_storage FROM pg_database;

→'8 GB'

⚠Anti-Pattern— Using pg_database_size() as a proxy for disk space remaining

pg_database_size() returns the logical size of the database within PostgreSQL. It does not account for transaction log (WAL), base backups, or OS-level filesystem overhead.

✓ Instead: Monitor actual disk usage at the OS level (df -h or pg_ls_dir equivalent). Use pg_database_size() only for PostgreSQL-level storage planning.

Use `pg_database_size(current_database())` to get the size of the current database without hardcoding its name — useful in scripts that run across multiple databases.

example

SELECT current_database() AS db, pg_size_pretty(pg_database_size(current_database())) AS size;

→Current database name and size

pg_describe_object

PG 9.0+→ text

Returns a human-readable description of a database object identified by its catalog OID (classid), object OID, and sub-object ID (e.g., column number). Used with pg_depend and pg_shdepend to understand dependency chains.

DBADeveloper

Signature

pg_describe_object ( classid oid, objid oid, objsubid integer ) → text

Parameters

Parameter	Type	Description
classid	oid	OID of the system catalog containing the object (e.g., 'pg_class'::regclass)
objid	oid	OID of the specific object within that catalog
objsubid	integer	Sub-object identifier (0 for the object itself, column number for columns)

Examples

sql

SELECT pg_describe_object(classid, objid, objsubid) AS depends_on FROM pg_depend WHERE refobjid = 'public.orders'::regclass;

→table public.orders, index orders_pkey, trigger audit_update, ...

sql

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS referenced_object, count(*) FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = 'public.my_func'::regproc GROUP BY 1 ORDER BY 2 DESC;

→Objects that my_func depends on, sorted by dependency count

sql

SELECT pg_describe_object(classid, objid, objsubid) FROM pg_depend WHERE refobjid = 'public.customer_status'::regtype AND deptype = 'n';

→All objects that depend on the customer_status type

sql

SELECT pg_describe_object(classid, objid, 0) AS object FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'uuid-ossp');

→All objects provided by the uuid-ossp extension

⚠Anti-Pattern— Manually joining pg_depend to multiple catalogs instead of using pg_describe_object

Resolving a dependency OID manually requires joining to pg_class, pg_proc, pg_type, pg_attribute, and more depending on the object type. `pg_describe_object` handles all catalog types internally and returns a consistent human-readable string.

✓ Instead: SELECT pg_describe_object(classid, objid, objsubid) FROM pg_depend WHERE refobjid = 'target_object'::regclass;

Before dropping a table, type, or function, query `pg_depend` filtered by `refobjid` and render each dependent object with `pg_describe_object`. This gives you the exact list of objects that CASCADE DROP will affect.

example

SELECT pg_describe_object(classid, objid, objsubid) AS will_also_drop FROM pg_depend WHERE refobjid = 'public.legacy_type'::regtype AND deptype = 'n';

→column employees.status, function validate_status(text), view active_employees

pg_get_constraintdef

PG 7.4+→ text

Returns the definition of a table constraint (CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY, EXCLUSION). Returns the constraint expression as it would appear in ALTER TABLE ... ADD CONSTRAINT.

DBA

Signatures

pg_get_constraintdef ( constraint oid ) → text

pg_get_constraintdef ( constraint oid, pretty boolean ) → text

Parameters

Parameter	Type	Description
constraint	oid	OID from pg_constraint.oid
pretty	boolean	If true, format with indentation

Examples

All constraints on orders table

sql

SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'orders'::regclass;

→(rows)

CHECK constraints only

sql

SELECT conname, pg_get_constraintdef(oid, true) FROM pg_constraint WHERE conrelid = 'products'::regclass AND contype = 'c';

→CHECK (price > 0)

Foreign key definitions

sql

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='orders'::regclass AND contype='f';

→FOREIGN KEY (customer_id) REFERENCES customers(id)

Unique constraints

sql

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='users'::regclass AND contype='u';

→UNIQUE (email)

Generate ALTER TABLE statements for migration

sql

SELECT 'ALTER TABLE orders ADD CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(oid) || ';' FROM pg_constraint WHERE conrelid='orders'::regclass;

→(rows)

⚠Anti-Pattern— Reading constraint details field-by-field from pg_constraint

pg_constraint stores conkey (column OID arrays), confkey, etc. in raw array form that requires multiple joins and array operations to decode into human-readable form.

✓ Instead: Use pg_get_constraintdef(oid) for the complete, immediately usable constraint definition string.

Combine format_type(), col_description(), and pg_get_constraintdef() to build comprehensive CREATE TABLE scripts from catalog metadata without pg_dump.

example

SELECT 'ALTER TABLE ' || conrelid::regclass || ' ADD CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(oid) || ';' FROM pg_constraint WHERE connamespace='public'::regnamespace AND contype='f';

→All foreign key ALTER TABLE statements for public schema

Common Gotchas

⚠

Foreign keys don't auto-create an index on the referencing column

PostgreSQL creates an index on the primary key side of a FK, but NOT on the foreign key column. Missing FK indexes cause slow lookups and lock escalation on DELETE.

⚠

lock_timeout vs statement_timeout — different problems, often confused

statement_timeout cancels a query that runs too long. lock_timeout cancels a query that waits too long to acquire a lock. You almost always want both set together.

pg_get_expr

PG 8.4+→ text

Decompiles an internal expression stored in pg_node_tree format back into human-readable SQL. Used with pg_index.indexprs (partial index expressions), pg_attrdef.adbin (column defaults), and pg_partitioned_table.partexprs (partition key expressions).

DeveloperDBA

Signatures

pg_get_expr ( pg_node_tree text, relation oid ) → text

pg_get_expr ( pg_node_tree text, relation oid, pretty boolean ) → text

Parameters

Parameter	Type	Description
pg_node_tree	text (pg_node_tree)	Internal expression as stored in system catalog columns
relation	oid	OID of the relation the expression belongs to (for column name resolution)
pretty	boolean	If true, enable indented output for complex expressions (optional)

Examples

sql

SELECT pg_get_expr(adbin, adrelid) AS column_default FROM pg_attrdef d JOIN pg_attribute a ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = 'public.orders'::regclass;

→nextval('orders_id_seq'::regclass) — column default expression

sql

SELECT indexrelid::regclass AS index, pg_get_expr(indexprs, indrelid) AS expr FROM pg_index WHERE indexprs IS NOT NULL AND indrelid = 'public.events'::regclass;

→lower((email)::text) — functional index expression

sql

SELECT pg_get_expr(indpred, indrelid) AS partial_condition FROM pg_index WHERE indpred IS NOT NULL AND indrelid = 'public.orders'::regclass;

→((status)::text = 'active'::text) — partial index WHERE clause

sql

SELECT pg_get_expr(pt.partexprs, pt.partrelid) AS partition_key FROM pg_partitioned_table pt JOIN pg_class c ON c.oid = pt.partrelid WHERE c.relname = 'events';

→date_trunc('month', created_at) — expression-based partition key

⚠Anti-Pattern— Reading pg_attrdef.adbin as a string directly

`pg_attrdef.adbin` stores expressions in an opaque internal tree format — it is not human-readable SQL. Treating it as a string or pattern-matching against it will fail. Always use `pg_get_expr(adbin, adrelid)` to decompile it.

✓ Instead: SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'my_table'::regclass;

Functional indexes store expressions in `pg_index.indexprs`. Use `pg_get_expr(indexprs, indrelid)` to retrieve the human-readable form and verify that indexes on computed expressions match your application's query patterns.

example

SELECT c.relname AS table, i.relname AS index, pg_get_expr(ix.indexprs, ix.indrelid) AS expression FROM pg_index ix JOIN pg_class c ON c.oid = ix.indrelid JOIN pg_class i ON i.oid = ix.indexrelid WHERE ix.indexprs IS NOT NULL AND c.relnamespace = 'public'::regnamespace;

→Table, index name, and expression for every functional index in the public schema

Common Gotchas

⚠

LIKE is case-sensitive; ILIKE is not — and LIKE is faster

LIKE 'hello%' will not match 'Hello'. Use ILIKE for case-insensitive pattern matching, but expect a performance cost.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

Implicit type casts in indexes — your index may not be used

WHERE col = 5 may not use an index on col (text type) because the integer 5 is cast to text, preventing index use.

pg_get_function_arguments

PG 8.4+→ text

Returns the argument list (with defaults) of a function in the form expected after the function name in CREATE FUNCTION. Useful for programmatic function inspection.

DBADeveloper

Signature

pg_get_function_arguments ( func oid ) → text

Parameters

Parameter	Type	Description
func	oid	OID of the function from pg_proc

Examples

Full argument list with defaults

sql

SELECT pg_get_function_arguments('my_func'::regproc);

→x integer, y text DEFAULT NULL

All function signatures in public schema

sql

SELECT proname, pg_get_function_arguments(oid) FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

→(rows)

All overloads of a function

sql

SELECT pg_get_function_arguments(oid) FROM pg_proc WHERE proname = 'overloaded_func';

→(multiple rows)

Build function signatures programmatically

sql

SELECT 'CREATE OR REPLACE FUNCTION ' || proname || '(' || pg_get_function_arguments(oid) || ')' FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

→(rows)

Function name, args, and return type together

sql

SELECT p.proname, pg_get_function_arguments(p.oid), pg_get_function_result(p.oid) FROM pg_proc p WHERE p.pronamespace = 'myapp'::regnamespace;

→(rows)

⚠Anti-Pattern— Parsing argument types from pg_proc.proargtypes directly

pg_proc.proargtypes stores OIDs as an oidvector; converting manually requires joins with pg_type and handling arrays, variadic args, and defaults separately.

✓ Instead: Use pg_get_function_arguments(oid) which handles all edge cases and returns a human-readable string.

Combine pg_get_function_arguments() and pg_get_function_result() with proname to get the complete function signature for documentation, API generation, or schema diffing.

example

SELECT proname || '(' || pg_get_function_arguments(oid) || ') RETURNS ' || pg_get_function_result(oid) FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

→Complete function signatures for all public functions

pg_get_function_identity_arguments

PG 8.4+→ text

Returns the argument list needed to uniquely identify a function (without default values). Unlike pg_get_function_arguments which includes defaults, this function returns only the type signature used to resolve overloaded functions — suitable for use in DDL statements like DROP FUNCTION or ALTER FUNCTION.

DeveloperDBA

Signature

pg_get_function_identity_arguments ( func oid ) → text

Parameters

Parameter	Type	Description
func	oid	OID of the function (from pg_proc.oid)

Examples

sql

SELECT pg_get_function_identity_arguments(oid) FROM pg_proc WHERE proname = 'process_order';

→integer, numeric

sql

SELECT format('DROP FUNCTION public.%I(%s);', proname, pg_get_function_identity_arguments(oid)) FROM pg_proc WHERE pronamespace = 'public'::regnamespace AND proname = 'old_func';

→DROP FUNCTION public.old_func(integer, text);

sql

SELECT proname, pg_get_function_identity_arguments(oid) AS identity_args, pg_get_function_arguments(oid) AS full_args FROM pg_proc WHERE proname = 'create_user';

→Shows identity args without defaults vs full args with defaults

sql

SELECT pg_get_function_identity_arguments(oid) FROM pg_proc WHERE oid = 'public.my_func(integer, text DEFAULT NULL)'::regprocedure;

→integer, text — defaults stripped

sql

SELECT count(*) FROM pg_proc WHERE pronamespace = 'public'::regnamespace GROUP BY pg_get_function_identity_arguments(oid) HAVING count(*) > 1;

→Detects truly duplicate function signatures (should be 0)

⚠Anti-Pattern— Using pg_get_function_arguments in DROP FUNCTION scripts

`pg_get_function_arguments` includes default value expressions (e.g., `p text DEFAULT 'none'`). PostgreSQL's DROP FUNCTION does not accept default expressions in the signature — this will cause a parse error.

✓ Instead: Always use `pg_get_function_identity_arguments(oid)` when constructing DROP FUNCTION or ALTER FUNCTION statements programmatically.

When generating DROP FUNCTION statements in migration scripts, use `pg_get_function_identity_arguments` (not the full argument form) to produce the minimal signature that uniquely identifies the function without default-value text that may differ between environments.

example

SELECT format('DROP FUNCTION IF EXISTS %I.%I(%s);', n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'public' AND p.proname LIKE 'legacy_%';

→DROP FUNCTION IF EXISTS public.legacy_process(integer, text);

Common Gotchas

⚠

Sequences don't roll back — gaps appear after any aborted transaction

SERIAL/SEQUENCE values are consumed even if the transaction rolls back. You'll see gaps in IDs — this is expected and correct behavior.

pg_get_function_result

PG 8.4+→ text

Returns the return type clause of a function as it would appear in a CREATE FUNCTION statement. Includes RETURNS TABLE(...) or RETURNS SETOF syntax when applicable, and shows OUT parameter names and types.

DeveloperDBA

Signature

pg_get_function_result ( func oid ) → text

Parameters

Parameter	Type	Description
func	oid	OID of the function (from pg_proc.oid)

Examples

sql

SELECT pg_get_function_result(oid) FROM pg_proc WHERE proname = 'get_orders';

→TABLE(order_id integer, total numeric, created_at timestamptz)

sql

SELECT pg_get_function_result('public.calculate_tax'::regprocedure);

→numeric

sql

SELECT proname, pg_get_function_result(oid) AS returns FROM pg_proc WHERE pronamespace = 'public'::regnamespace ORDER BY proname;

→Catalog of return types for all public-schema functions

sql

SELECT pg_get_function_result(oid) FROM pg_proc WHERE proname = 'generate_report';

→SETOF report_row

sql

SELECT proname, pg_get_function_arguments(oid), pg_get_function_result(oid) FROM pg_proc WHERE pronamespace = current_schema()::regnamespace;

→Full function signature catalog for current schema

⚠Anti-Pattern— Using pg_proc.prorettype directly instead of pg_get_function_result

`pg_proc.prorettype` is a raw OID. For table-returning functions and OUT-parameter functions, the actual return shape is encoded in `proargmodes` and `proargnames`, not just `prorettype`. Querying `prorettype` alone misses this detail.

✓ Instead: SELECT pg_get_function_result(oid) FROM pg_proc WHERE proname = 'my_func';

Use `pg_get_function_arguments` for the parameter list and `pg_get_function_result` for the return type to reconstruct a complete, human-readable function signature — useful for documentation generation or diff reports between environments.

example

SELECT format('%s(%s) → %s', proname, pg_get_function_arguments(oid), pg_get_function_result(oid)) FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

→get_orders(customer_id integer) → TABLE(order_id integer, total numeric)

pg_get_functiondef

PG 8.4+→ text

Returns the full CREATE OR REPLACE FUNCTION statement that defines a function or procedure. Useful for function inspection, auditing, and generating migration scripts.

DBADeveloper

Signature

pg_get_functiondef ( func oid ) → text

Parameters

Parameter	Type	Description
func	oid	OID of the function from pg_proc.oid

Examples

Get definition using regprocedure cast

sql

SELECT pg_get_functiondef('my_function'::regprocedure);

→CREATE OR REPLACE FUNCTION ...

Get definition by function name

sql

SELECT pg_get_functiondef(p.oid) FROM pg_proc p WHERE p.proname = 'calculate_tax';

→CREATE OR REPLACE FUNCTION calculate_tax ...

All function definitions in a schema

sql

SELECT proname, pg_get_functiondef(oid) FROM pg_proc WHERE pronamespace = 'myapp'::regnamespace;

→(rows)

Disambiguate overloaded functions by signature

sql

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE oid = 'my_func(integer, text)'::regprocedure;

→CREATE OR REPLACE FUNCTION ...

List PL/pgSQL function source directly from pg_proc

sql

SELECT proname, prosrc FROM pg_proc WHERE pronamespace = 'public'::regnamespace AND prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql');

→(rows)

⚠Anti-Pattern— Reading prosrc from pg_proc for function definitions

pg_proc.prosrc only contains the function body, not headers (language, volatility, security, arguments). pg_get_functiondef() returns the complete, immediately executable DDL.

✓ Instead: Use pg_get_functiondef(oid) for complete, runnable function DDL including all modifiers.

Combine pg_get_functiondef() with pg_proc to extract all function definitions in a schema for migration scripts, backups, or cross-environment comparison.

example

SELECT pg_get_functiondef(oid) || ';' FROM pg_proc WHERE pronamespace = 'myapp'::regnamespace AND prokind = 'f';

→Complete CREATE FUNCTION statements for all functions in myapp schema

Common Gotchas

⚠

lock_timeout vs statement_timeout — different problems, often confused

statement_timeout cancels a query that runs too long. lock_timeout cancels a query that waits too long to acquire a lock. You almost always want both set together.

pg_get_indexdef

PG 7.4+→ text

Returns the CREATE INDEX statement that defines an index. With column and pretty parameters, returns only the index expression for a specific column. Useful for index auditing and schema migration scripts.

DBA

Signatures

pg_get_indexdef ( index oid ) → text

pg_get_indexdef ( index oid, column integer, pretty boolean ) → text

Parameters

Parameter	Type	Description
index	oid	OID of the index from pg_class
column	integer	Column number (1-based); returns only that column expression if specified
pretty	boolean	If true, format output with indentation

Examples

Full CREATE INDEX statement

sql

SELECT pg_get_indexdef('orders_user_id_idx'::regclass);

→CREATE INDEX orders_user_id_idx ON public.orders USING btree (user_id)

All index definitions in public schema

sql

SELECT indexname, pg_get_indexdef(indexrelid) FROM pg_indexes JOIN pg_class c ON c.relname=indexname WHERE c.relnamespace='public'::regnamespace;

→(rows)

All indexes on a specific table

sql

SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = 'products'::regclass;

→(rows)

Only unique indexes on users table

sql

SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indisunique AND indrelid = 'users'::regclass;

→CREATE UNIQUE INDEX ...

First column expression of each index (for functional indexes)

sql

SELECT indexname, pg_get_indexdef(c.oid, 1, true) AS first_col_expr FROM pg_indexes ix JOIN pg_class c ON c.relname=ix.indexname WHERE ix.tablename='orders';

→(rows)

⚠Anti-Pattern— Reading index definitions from pg_indexes view only

pg_indexes.indexdef is already a preformatted string, but it may omit some details for partial indexes (WHERE clause) or functional indexes. pg_get_indexdef() always returns the complete definition.

✓ Instead: Use pg_get_indexdef(indexrelid) from pg_index for authoritative, complete index DDL.

The column-specific form pg_get_indexdef(oid, col, true) is invaluable for inspecting functional indexes — it shows the exact expression indexed rather than just a column name.

example

SELECT pg_get_indexdef(indexrelid, generate_subscripts(indkey,1), true) AS expr FROM pg_index WHERE indrelid='orders'::regclass AND indexprs IS NOT NULL;

→Expression definition for each functional index

Common Gotchas

⚠

LIKE is case-sensitive; ILIKE is not — and LIKE is faster

LIKE 'hello%' will not match 'Hello'. Use ILIKE for case-insensitive pattern matching, but expect a performance cost.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

Implicit type casts in indexes — your index may not be used

WHERE col = 5 may not use an index on col (text type) because the integer 5 is cast to text, preventing index use.

pg_get_ruledef

PG 8.4+→ text

Returns the defining CREATE RULE statement for the specified rule OID. Rules are PostgreSQL's query rewrite system, underlying all view definitions. The optional pretty parameter enables indented output.

DeveloperDBA

Signatures

pg_get_ruledef ( rule oid ) → text

pg_get_ruledef ( rule oid, pretty boolean ) → text

Parameters

Parameter	Type	Description
rule	oid	OID of the rule from pg_rewrite.oid
pretty	boolean	If true, format with line breaks and indentation (optional)

Examples

sql

SELECT pg_get_ruledef(r.oid) FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class WHERE c.relname = 'active_orders' AND r.rulename = '_RETURN';

→CREATE RULE _RETURN AS ON SELECT TO active_orders DO INSTEAD SELECT ...

sql

SELECT rulename, pg_get_ruledef(oid, true) AS def FROM pg_rewrite WHERE ev_class = 'public.orders'::regclass;

→Pretty-printed rule definitions for the orders table

sql

SELECT r.rulename, pg_get_ruledef(r.oid) FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class WHERE c.relkind = 'v' AND c.relnamespace = 'public'::regnamespace;

→Rule definitions for all views in the public schema

sql

SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE rulename = 'log_inserts' AND ev_class = 'public.audit_table'::regclass;

→CREATE RULE log_inserts AS ON INSERT TO audit_table DO ALSO INSERT INTO audit_log ...

⚠Anti-Pattern— Writing INSTEAD rules as an alternative to views

Custom INSTEAD rules on tables are a historical PostgreSQL feature that predates proper views. They are complex, hard to debug, and can interact unexpectedly with triggers, CTEs, and RETURNING clauses. Modern PostgreSQL views with INSTEAD OF triggers are far more predictable.

✓ Instead: CREATE VIEW v AS SELECT ...; CREATE TRIGGER instead_insert INSTEAD OF INSERT ON v FOR EACH ROW EXECUTE FUNCTION handle_insert();

For views specifically, `pg_get_viewdef` gives a cleaner, SELECT-only representation. Use `pg_get_ruledef` when you need to inspect non-view rules (INSERT/UPDATE/DELETE rewrite rules) or when you need the full CREATE RULE syntax for scripting.

example

SELECT pg_get_viewdef('public.active_orders', true);

→Clean SELECT statement underlying the view

pg_get_serial_sequence

PG 8.0+→ text

Returns the name of the sequence associated with a SERIAL/BIGSERIAL/SMALLSERIAL or GENERATED BY DEFAULT AS IDENTITY column. Returns NULL if no sequence is associated.

DeveloperDBA

Signature

pg_get_serial_sequence ( table text, column text ) → text

Parameters

Parameter	Type	Description
table	text	Table name (optionally schema-qualified)
column	text	Column name

Examples

Name of the sequence behind orders.id serial

sql

SELECT pg_get_serial_sequence('orders', 'id');

→public.orders_id_seq

Current sequence value after getting its name

sql

SELECT last_value FROM orders_id_seq;

→10045

Reset sequence to match existing max after bulk import

sql

SELECT setval(pg_get_serial_sequence('orders','id'), MAX(id)) FROM orders;

→10045

UUID columns have no sequence

sql

SELECT pg_get_serial_sequence('users', 'uuid_col');

→NULL

Get current sequence value dynamically

sql

SELECT currval(pg_get_serial_sequence('products', 'id'));

→500

⚠Anti-Pattern— Hardcoding sequence names in application code

SERIAL columns use predictable names like tablename_colname_seq, but this is a convention, not a guarantee. The sequence can be renamed or replaced.

✓ Instead: Always use pg_get_serial_sequence('table','col') to dynamically resolve the sequence name.

Bulk loads using COPY or INSERT with explicit IDs do not advance the serial sequence, causing duplicate key errors on the next INSERT. Always run setval() after bulk loads.

example

SELECT setval(pg_get_serial_sequence('products', 'id'), COALESCE(MAX(id), 0) + 1, false) FROM products;

→Sequence reset to max(id)+1 to prevent duplicate key errors

Common Gotchas

⚠

Sequences don't roll back — gaps appear after any aborted transaction

SERIAL/SEQUENCE values are consumed even if the transaction rolls back. You'll see gaps in IDs — this is expected and correct behavior.

pg_get_triggerdef

PG 8.4+→ text

Returns the CREATE TRIGGER statement defining the specified trigger. Useful for extracting trigger definitions for documentation, diffing, or migration scripts.

DeveloperDBA

Signatures

pg_get_triggerdef ( trigger oid ) → text

pg_get_triggerdef ( trigger oid, pretty boolean ) → text

Parameters

Parameter	Type	Description
trigger	oid	OID of the trigger from pg_trigger.oid
pretty	boolean	If true, format output with indentation (optional)

Examples

sql

SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'audit_update' AND tgrelid = 'public.orders'::regclass;

→CREATE TRIGGER audit_update AFTER UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION log_changes()

sql

SELECT tgname, pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'public.employees'::regclass AND NOT tgisinternal;

→Pretty-printed trigger definitions for the employees table

sql

SELECT c.relname, t.tgname, pg_get_triggerdef(t.oid) FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid WHERE c.relnamespace = 'public'::regnamespace AND NOT t.tgisinternal ORDER BY c.relname, t.tgname;

→All user-defined trigger definitions in the public schema

sql

SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgname = 'set_updated_at';

→Pretty-printed CREATE TRIGGER with WHEN clause and timing

⚠Anti-Pattern— Reading trigger definitions from information_schema.triggers instead of pg_get_triggerdef

`information_schema.triggers` shows trigger metadata but not the full WHEN clause or the complete CREATE TRIGGER text. For migration scripts or complete trigger documentation, `pg_get_triggerdef` is the correct source.

✓ Instead: SELECT pg_get_triggerdef(t.oid, true) FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid WHERE c.relname = 'orders' AND NOT t.tgisinternal;

PostgreSQL creates internal triggers for deferred constraints and foreign keys. Filter them with `WHERE NOT tgisinternal` to show only user-defined triggers. Internal trigger OIDs are still valid inputs to `pg_get_triggerdef`.

example

SELECT tgname, pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal;

→Only user-created triggers on the orders table

Common Gotchas

⚠

lock_timeout vs statement_timeout — different problems, often confused

statement_timeout cancels a query that runs too long. lock_timeout cancels a query that waits too long to acquire a lock. You almost always want both set together.

pg_get_viewdef

PG 7.4+→ text

Returns the underlying SELECT statement of a view or materialised view. Used for view inspection, documentation, and schema migration. Alias and column names in the view definition are preserved.

DBADeveloper

Signatures

pg_get_viewdef ( view oid ) → text

pg_get_viewdef ( view oid, pretty boolean ) → text

pg_get_viewdef ( view name ) → text

pg_get_viewdef ( view name, pretty boolean ) → text

Parameters

Parameter	Type	Description
view	oid or name	View OID or name (optionally schema-qualified)
pretty	boolean	If true, format with indentation for readability

Examples

Pretty-printed view definition

sql

SELECT pg_get_viewdef('active_customers', true);

→

 SELECT id, name, email
   FROM customers
  WHERE status = 'active';

Compact form using regclass cast

sql

SELECT pg_get_viewdef('active_customers'::regclass);

→SELECT id, name, email FROM customers WHERE status = 'active';

All view definitions in public schema

sql

SELECT viewname, pg_get_viewdef(viewname::regclass, true) FROM pg_views WHERE schemaname = 'public';

→(rows)

Materialised view definitions (relkind = m)

sql

SELECT relname, pg_get_viewdef(oid) FROM pg_class WHERE relkind = 'm' AND relnamespace = 'public'::regnamespace;

→(rows)

Find views that reference a sensitive table

sql

SELECT pg_get_viewdef(oid) ~ 'sensitive_table' AS uses_sensitive FROM pg_class WHERE relkind IN ('v','m');

→(rows)

⚠Anti-Pattern— Reading view definition from pg_views.definition directly

pg_views.definition is convenient but uses the same underlying function. For materialised views (relkind='m') which do not appear in pg_views, pg_get_viewdef() is the only option.

✓ Instead: Use pg_get_viewdef(oid) consistently for both regular views and materialised views.

Before dropping a table, use pg_get_viewdef() to check which views reference it: SELECT relname FROM pg_class WHERE relkind IN ('v','m') AND pg_get_viewdef(oid) ~ 'my_table'.

example

SELECT relname FROM pg_class WHERE relkind IN ('v','m') AND relnamespace='public'::regnamespace AND pg_get_viewdef(oid) ~ 'orders';

→All views that depend on the orders table

Common Gotchas

⚠

lock_timeout vs statement_timeout — different problems, often confused

statement_timeout cancels a query that runs too long. lock_timeout cancels a query that waits too long to acquire a lock. You almost always want both set together.

pg_has_role

PG 7.4+→ boolean

Tests whether a user has a specific privilege for a role. Privilege types: MEMBER (direct or inherited membership), USAGE (can SET ROLE), MEMBER WITH ADMIN OPTION. Accounts for role inheritance.

DBA

Signatures

pg_has_role ( user name/oid, role name/oid, privilege text ) → boolean

pg_has_role ( role name/oid, privilege text ) → boolean

Parameters

Parameter	Type	Description
user	name or oid	User to test; defaults to current_user
role	name or oid	Role to check membership in
privilege	text	MEMBER, USAGE, or MEMBER WITH ADMIN OPTION

Examples

Alice is (directly or transitively) a member of admin_role

sql

SELECT pg_has_role('alice', 'admin_role', 'MEMBER');

→true

Current user cannot SET ROLE admin_role

sql

SELECT pg_has_role('admin_role', 'USAGE');

→false

Bob cannot grant superadmin to others

sql

SELECT pg_has_role('bob', 'superadmin', 'MEMBER WITH ADMIN OPTION');

→false

All roles current user belongs to (including inherited)

sql

SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, rolname, 'MEMBER');

→(rows)

app_user can SET ROLE readonly

sql

SELECT pg_has_role('app_user', 'readonly', 'USAGE');

→true

⚠Anti-Pattern— Checking pg_auth_members directly for effective membership

pg_auth_members only shows direct role memberships. To check transitive membership (user → role_a → target_role), you need recursive CTEs or pg_has_role().

✓ Instead: Use pg_has_role(username, target_role, 'MEMBER') to check effective (transitive) membership in one call.

pg_has_role accounts for role inheritance (INHERIT). It will return true even if a user is a member of a role that is a member of the target role — unlike checking pg_auth_members directly.

example

SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, rolname, 'MEMBER') ORDER BY rolname;

→All roles the current user effectively belongs to (direct + inherited)

Common Gotchas

⚠

Row-Level Security is silently bypassed by table OWNER and superusers

RLS policies don't apply to the table owner or superusers by default. Your 'secure' RLS schema has a backdoor if app code connects as the owner.

pg_identify_object

PG 9.1+→ record (type text, schema text, name text, identity text)

Returns a record describing a database object. Unlike pg_describe_object which returns a single human-readable string, pg_identify_object returns structured fields: type (object kind), schema, name, and identity (the unique identifier suitable for DDL).

DBADeveloper

Signature

pg_identify_object ( classid oid, objid oid, objsubid integer ) → record ( type text, schema text, name text, identity text )

Parameters

Parameter	Type	Description
classid	oid	OID of the system catalog (e.g., 'pg_class'::regclass, 'pg_proc'::regclass)
objid	oid	OID of the object within that catalog
objsubid	integer	Sub-object ID (0 for whole object, column number for columns)

Examples

sql

SELECT (pg_identify_object(classid, objid, objsubid)).* FROM pg_depend WHERE refobjid = 'public.orders'::regclass AND deptype = 'n' LIMIT 5;

→type: 'index', schema: 'public', name: 'orders_pkey', identity: 'public.orders_pkey'

sql

SELECT o.type, o.schema, o.name FROM pg_depend d, pg_identify_object(d.classid, d.objid, d.objsubid) o WHERE d.refobjid = 'public.order_status'::regtype;

→All objects referencing the order_status type with their structured metadata

sql

SELECT o.type, o.identity FROM pg_shdepend d, pg_identify_object(d.classid, d.objid, d.objsubid) o WHERE d.refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'old_owner');

→All objects owned by old_owner that need ownership transfer

sql

SELECT o.type, o.identity FROM pg_depend d, pg_identify_object(d.refclassid, d.refobjid, d.refobjsubid) o WHERE d.classid = 'pg_proc'::regclass AND d.objid = 'public.my_func'::regproc;

→Structured identity of every object my_func depends on

⚠Anti-Pattern— Parsing pg_describe_object output instead of using pg_identify_object

The text from `pg_describe_object` is designed for human reading, not machine parsing. Its format can vary by object type and PostgreSQL version. Use `pg_identify_object` to get structured fields (type, schema, name, identity) for programmatic use.

✓ Instead: SELECT o.type, o.schema, o.name, o.identity FROM pg_depend d, pg_identify_object(d.classid, d.objid, d.objsubid) o WHERE d.refobjid = 'target'::regclass;

The `identity` field returned by `pg_identify_object` is a schema-qualified, DDL-safe name suitable for use in ALTER, DROP, or GRANT statements. Unlike the `name` field (which may be unqualified), `identity` always includes the schema.

example

SELECT format('GRANT SELECT ON %s TO reporting;', o.identity) FROM pg_class c, pg_identify_object('pg_class'::regclass, c.oid, 0) o WHERE c.relkind = 'r' AND c.relnamespace = 'public'::regnamespace;

→GRANT SELECT ON public.orders TO reporting; ...

pg_input_is_valid

PG 16+→ boolean

Tests whether a string is a valid input representation for the specified data type. Returns true if valid, false if not — without raising an error. Useful for safe data validation before casting. Added in PostgreSQL 16.

DeveloperDBAData Eng

Signature

pg_input_is_valid ( string text, type text ) → boolean

Parameters

Parameter	Type	Description
string	text	Input string to validate
type	text	Target data type name (e.g., 'integer', 'date', 'inet', 'uuid')

Examples

sql

SELECT pg_input_is_valid('42', 'integer');

→true

sql

SELECT pg_input_is_valid('not_a_number', 'integer');

→false — no exception raised

sql

SELECT pg_input_is_valid('2025-13-01', 'date');

→false — month 13 is invalid

sql

SELECT pg_input_is_valid('192.168.1.256', 'inet');

→false — octet 256 out of range

sql

SELECT raw_value, pg_input_is_valid(raw_value, 'uuid') AS is_valid_uuid FROM import_staging WHERE source = 'external_api';

→Validate UUID columns from external data before casting

⚠Anti-Pattern— Using REGEXP checks to simulate type validation

Writing regex patterns to validate date strings, IP addresses, or UUIDs is error-prone and never fully correct. `pg_input_is_valid` uses the actual type input parser, so it matches exactly what PostgreSQL would accept — including locale-sensitive number formats and all edge cases.

✓ Instead: SELECT pg_input_is_valid(raw_date, 'date') FROM staging; -- Instead of regex date validation

`pg_input_is_valid` tells you if input is valid. `pg_input_error_info` (also PG16+) returns the exact error message, detail, hint, and SQLSTATE when it isn't. Use both together to validate and report errors without exception handling.

example

SELECT pg_input_is_valid(v, 'integer') AS ok, (pg_input_error_info(v, 'integer')).message AS error FROM (VALUES ('42'), ('bad'), ('99')) t(v);

→true/NULL | false/'invalid input syntax for type integer: "bad"' | true/NULL

pg_postmaster_start_time

PG 8.2+→ timestamp with time zone

Returns the time at which the server started.

DBA

Signature

pg_postmaster_start_time ( ) → timestamp with time zone

Examples

sql

SELECT pg_postmaster_start_time();

→2025-03-07 08:00:00+00

sql

SELECT now() - pg_postmaster_start_time() AS uptime;

→Server uptime as interval

Combine with other diagnostics in health check

sql

SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size, extract(epoch FROM now() - pg_postmaster_start_time())::int AS uptime_seconds;

→'2 GB' | 86400

Detect recent restart for alerting

sql

SELECT pg_postmaster_start_time() > now() - interval '5 minutes' AS recently_restarted;

→false

⚠Anti-Pattern— Using pg_postmaster_start_time() to detect restarts in application code

Polling pg_postmaster_start_time() from application code to detect server restarts adds unnecessary queries to every connection check.

✓ Instead: Use connection error handling to detect restarts. Check pg_postmaster_start_time() only in monitoring dashboards or health check endpoints, not in hot application paths.

Use `justify_interval(now() - pg_postmaster_start_time())` for a human-friendly uptime display like '3 days 14:32:00'.

example

SELECT justify_interval(now() - pg_postmaster_start_time()) AS server_uptime;

→"3 days 14:32:00"

pg_relation_size

PG 8.1+→ bigint

Returns the on-disk size in bytes of the specified fork of a relation (table, index, etc.). Fork: 'main' (default), 'vm', 'fsm', 'init'.

DBAReliability

Signature

pg_relation_size ( regclass [, text] ) → bigint

Parameters

Parameter	Type	Description
relation	regclass	Table, index, or other relation
fork	text	Storage fork: 'main' (default), 'vm' (visibility map), 'fsm' (free space map)

Examples

sql

SELECT pg_relation_size('orders');

→8192 (bytes)

sql

SELECT pg_size_pretty(pg_relation_size('orders'));

→8192 bytes

sql

SELECT pg_size_pretty(pg_total_relation_size('orders'));

→2 MB (includes indexes and TOAST)

Compare heap vs index sizes for all public tables

sql

SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS heap, pg_size_pretty(pg_indexes_size(oid)) AS indexes FROM pg_class WHERE relnamespace = 'public'::regnamespace AND relkind = 'r';

→(table heap vs index sizes)

⚠Anti-Pattern— Using pg_relation_size() to measure total table storage

pg_relation_size() returns only the main table heap (MAIN fork). It excludes indexes, TOAST tables, and visibility maps — so it severely undercounts total storage for wide or highly-indexed tables.

✓ Instead: Use pg_total_relation_size() for the complete storage footprint including all indexes and TOAST. Use pg_relation_size() only when you specifically need the heap-only size.

`pg_relation_size` returns just the main data file. Use `pg_total_relation_size` to include indexes, TOAST tables, and all forks — this gives the actual storage footprint.

example

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total_size FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

→Top 10 largest tables by total size

Common Gotchas

⚠

VACUUM FULL takes an exclusive lock — blocks all reads AND writes

VACUUM FULL rewrites the entire table and holds an AccessExclusiveLock for the duration. Production traffic is blocked.

⚠

pg_dump is consistent but still impacts production — it holds a snapshot

pg_dump takes a consistent snapshot, but that snapshot prevents autovacuum from removing dead tuples — causing table bloat during long backups.

pg_size_pretty

PG 8.1+→ text

Converts a size in bytes to a human-readable format (e.g., '2048 kB', '3 MB', '1.5 GB').

DBAReliability

Signatures

pg_size_pretty ( bigint ) → text

pg_size_pretty ( numeric ) → text

Parameters

Parameter	Type	Description
bytes	bigint or numeric	Size in bytes to format

Examples

sql

SELECT pg_size_pretty(1024);

→1024 bytes

sql

SELECT pg_size_pretty(1048576);

→1024 kB

sql

SELECT pg_size_pretty(pg_database_size('mydb'));

→156 MB

Add sizes arithmetically before formatting

sql

SELECT pg_size_pretty(pg_database_size(current_database()) + pg_total_relation_size('big_table'));

→'3542 MB'

⚠Anti-Pattern— Using pg_size_pretty() for programmatic size comparisons

pg_size_pretty() returns text like "2048 MB" — you cannot compare these strings numerically. Sorting or filtering by pg_size_pretty() output sorts lexicographically, not by actual size.

✓ Instead: Always compare and sort using the raw byte count: ORDER BY pg_total_relation_size(oid) DESC. Apply pg_size_pretty() only at the outermost SELECT for display.

Always wrap `pg_relation_size`, `pg_total_relation_size`, and `pg_database_size` in `pg_size_pretty()` for human-readable output in reports and monitoring queries.

example

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

→All tables with human-readable sizes

pg_total_relation_size

PG 8.1+→ bigint

Returns the total disk space used by the specified table, including all indexes and TOAST data.

DBAReliability

Signature

pg_total_relation_size ( regclass ) → bigint

Parameters

Parameter	Type	Description
relation	regclass	Table to measure

Examples

sql

SELECT pg_size_pretty(pg_total_relation_size('users'));

→24 MB

sql

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relkind='r' ORDER BY 2 DESC LIMIT 5;

→Top 5 largest tables

Total includes TOAST and all indexes

sql

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total, pg_size_pretty(pg_relation_size('orders')) AS heap_only;

→'2048 MB' | '1200 MB'

Find the largest tables by total size

sql

SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables WHERE schemaname='public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 5;

→(top 5 largest tables)

⚠Anti-Pattern— Summing pg_total_relation_size for partitions and their parent

For partitioned tables, pg_total_relation_size on the parent includes all partition sizes. Summing parent + each partition double-counts all data.

✓ Instead: For partitioned tables, call pg_total_relation_size() only on the parent table, or use pg_partition_tree() to enumerate partitions explicitly and sum individual partition sizes.

Record `pg_total_relation_size` in a metrics table periodically to track table growth rates and proactively plan for capacity.

example

INSERT INTO size_metrics SELECT now(), relname, pg_total_relation_size(oid) FROM pg_class WHERE relkind = 'r';

→Historical size tracking for all tables

pg_typeof

PG 8.4+→ regtype

Returns the data type of the supplied expression.

Developer

Signature

pg_typeof ( any ) → regtype

Parameters

Parameter	Type	Description
expression	any	Expression to inspect

Examples

sql

SELECT pg_typeof(42);

→integer

sql

SELECT pg_typeof(now());

→timestamp with time zone

sql

SELECT pg_typeof(ARRAY[1,2]);

→integer[]

Type check using regtype cast handles aliases

sql

SELECT col, pg_typeof(col) = 'integer'::regtype AS is_integer FROM mixed_data;

→(rows)

⚠Anti-Pattern— Using pg_typeof() for runtime type dispatch in production queries

pg_typeof() returns a regtype value. Comparing it with text strings using pg_typeof(x) = 'integer' works but requires a text cast and may miss type aliases (int vs integer).

✓ Instead: Compare with regtype: pg_typeof(x) = 'integer'::regtype — this handles aliases correctly. Or use format_type(pg_typeof(x)::oid, -1) for the canonical type name.

When function overloading or implicit casts cause unexpected behavior, wrap the expression in `pg_typeof()` to see exactly what type PostgreSQL resolved it to.

example

SELECT pg_typeof(1 / 2), pg_typeof(1.0 / 2), pg_typeof(1::numeric / 2);

→integer | numeric | numeric

pg_xact_commit_timestamp

PG 9.5+; requires track_commit_timestamp = on→ timestamp with time zone

Returns the commit timestamp of the specified transaction. Returns NULL if the transaction ID is too old (no longer tracked) or the server parameter track_commit_timestamp is off. Requires track_commit_timestamp = on in postgresql.conf.

DBADeveloper

Signature

pg_xact_commit_timestamp ( xid ) → timestamp with time zone

Parameters

Parameter	Type	Description
xid	xid	Transaction ID to look up (from xmin, xmax, or txid_current())

Examples

sql

SELECT pg_xact_commit_timestamp(xmin) AS committed_at FROM orders WHERE id = 42;

→2025-03-15 14:23:01+00 — when this row was first committed

sql

SELECT xmin, pg_xact_commit_timestamp(xmin) AS insert_time FROM users ORDER BY insert_time DESC LIMIT 10;

→10 most recently inserted rows by commit time

sql

SELECT id, pg_xact_commit_timestamp(xmin) FROM orders WHERE pg_xact_commit_timestamp(xmin) > NOW() - INTERVAL '1 hour';

→Rows committed in the last hour — a change feed without triggers

sql

SELECT pg_xact_commit_timestamp(xmin) FROM large_table WHERE xmin::text::bigint > txid_snapshot_xmin(txid_current_snapshot());

→Rows committed after the current snapshot began

⚠Anti-Pattern— Using pg_xact_commit_timestamp without enabling track_commit_timestamp

By default, `track_commit_timestamp` is off and the function always returns NULL. Teams sometimes mistake this for a bug. The setting requires a server restart to enable and has a small (~1-2%) performance overhead.

✓ Instead: -- Check if enabled: SHOW track_commit_timestamp; -- Enable (requires restart): -- In postgresql.conf: track_commit_timestamp = on

With `track_commit_timestamp = on`, you get a commit time for every row's `xmin` without any triggers or extra columns. Useful for change data capture, replication monitoring, and audit. Note: commit timestamps are retained only for a limited history window controlled by autovacuum.

example

-- Enable in postgresql.conf:
-- track_commit_timestamp = on
-- Then:
SELECT id, pg_xact_commit_timestamp(xmin) AS last_modified FROM customers WHERE xmin::text::bigint > $last_checked_xid;

→Rows modified since the last check, with their commit time

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

pg_xact_commit_timestamp_only

PG 14+→ record (timestamp timestamptz, roident oid)

Like pg_xact_commit_timestamp, but also returns the replication origin identifier (roident) and skips the visibility check performed by pg_xact_commit_timestamp. Useful in logical replication contexts where you need both the commit time and the origin of the transaction.

DBA

Signature

pg_xact_commit_timestamp_only ( xid ) → record ( timestamp timestamptz, roident oid )

Parameters

Parameter	Type	Description
xid	xid	Transaction ID to look up

Examples

sql

SELECT (pg_xact_commit_timestamp_only(xmin)).timestamp AS committed_at FROM events WHERE id = 1;

→2025-03-15 14:23:01+00

sql

SELECT ts.timestamp, ts.roident FROM events, pg_xact_commit_timestamp_only(xmin) ts WHERE events.id = 42;

→timestamp: 2025-03-15 14:23:01+00 | roident: 0 (local) or replication origin OID

sql

SELECT xmin, (pg_xact_commit_timestamp_only(xmin)).roident AS origin FROM replicated_events WHERE (pg_xact_commit_timestamp_only(xmin)).roident <> 0;

→Rows that arrived via logical replication (non-zero roident)

sql

SELECT r.roname, count(*) FROM events e, pg_xact_commit_timestamp_only(e.xmin) ts JOIN pg_replication_origin r ON r.roident = ts.roident WHERE ts.roident <> 0 GROUP BY 1;

→Count of replicated rows by replication origin name

⚠Anti-Pattern— Using pg_xact_commit_timestamp when you need origin info in a replication setup

`pg_xact_commit_timestamp` only returns the timestamp. In logical replication setups, different origins may commit at the same millisecond. Without the `roident`, you cannot determine whether a row was committed locally or arrived from a subscriber.

✓ Instead: SELECT ts.timestamp, ts.roident FROM my_table, pg_xact_commit_timestamp_only(xmin) ts WHERE ts.roident <> 0; -- Non-zero roident = replicated

In a logical replication setup, `roident` from `pg_xact_commit_timestamp_only` identifies which replication origin committed the transaction. Join with `pg_replication_origin` to map OIDs to human-readable origin names for multi-master or cascade replication setups.

example

SELECT r.roname AS source, count(*) FROM orders o, pg_xact_commit_timestamp_only(o.xmin) ts LEFT JOIN pg_replication_origin r ON r.roident = ts.roident GROUP BY 1;

→Count of orders by replication source (NULL = local)

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

session_user

PG 7.4+→ name

Returns the session user name — the user who authenticated to the database, unaffected by SET ROLE.

SecurityDBA

Signature

session_user → name

Examples

sql

SELECT session_user;

→alice

sql

SET ROLE bob; SELECT current_user, session_user;

→bob | alice

Log the actual login identity, unaffected by SET ROLE or SECURITY DEFINER

sql

INSERT INTO login_audit (login_user, logged_at) VALUES (session_user, now());

→(row inserted)

Inside SET ROLE admin: shows both identities

sql

SELECT current_user AS effective_role, session_user AS original_login FROM generate_series(1,1);

→admin | alice

⚠Anti-Pattern— Using session_user instead of current_user to check permissions

Inside SECURITY DEFINER functions, session_user is the caller but current_user is the function owner (who has elevated privileges). Permission checks should use current_user.

✓ Instead: Use current_user for permission and RLS checks. Use session_user only when you explicitly need to identify the original login user (e.g. for audit logging of who initiated the session).

Always log `session_user` (not `current_user`) in audit triggers to record who actually connected to the database, not just which role is currently active.

example

INSERT INTO audit_log (action, actor, ts) VALUES (TG_OP, session_user, now());

→Audit log with the authenticated user's name

txid_current

PG 8.1+; deprecated in PG 13 in favour of pg_current_xact_id()→ bigint

Returns the current transaction's ID as a 64-bit integer. Assigns a new transaction ID if none has been assigned yet. Deprecated in PostgreSQL 13 in favour of pg_current_xact_id() which returns the native xid8 type.

DeveloperDBA

Signature

txid_current ( ) → bigint

Examples

sql

SELECT txid_current();

→1234567 — current transaction ID as bigint

sql

BEGIN; SELECT txid_current(); SELECT txid_current(); COMMIT;

→Same value returned both times — stable within a transaction

sql

SELECT txid_current() AS xact_id, now() AS captured_at;

→1234567 | 2025-03-16 10:30:00+00

sql

-- Preferred PG13+ equivalent:
SELECT pg_current_xact_id();

→1234567 (as xid8)

⚠Anti-Pattern— Using txid_current() for long-running snapshot comparisons

Transaction IDs wrap around after ~2 billion transactions. Storing `txid_current()` as a bigint in a table and comparing it much later (across a wraparound boundary) will produce wrong results. Use `xid8` via `pg_current_xact_id()` for 64-bit epoch-aware IDs that never wrap.

✓ Instead: SELECT pg_current_xact_id(); -- returns xid8 which never wraps

In PostgreSQL 13+, prefer `pg_current_xact_id()` which returns `xid8` (64-bit native transaction ID type) instead of `bigint`. The xid8 type integrates better with other system catalog columns like `pg_snapshot` operations.

example

SELECT pg_current_xact_id();

→1234567 (as xid8, compatible with pg_snapshot_xip and pg_visible_in_snapshot)

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

txid_current_if_assigned

PG 9.0+; deprecated in PG 13 in favour of pg_current_xact_id_if_assigned()→ bigint

Returns the current transaction ID only if one has already been assigned (i.e., a write has occurred in this transaction). Returns NULL in read-only transactions that haven't been assigned an ID. Deprecated in PG13 in favour of pg_current_xact_id_if_assigned().

DeveloperDBA

Signature

txid_current_if_assigned ( ) → bigint

Examples

sql

BEGIN; SELECT txid_current_if_assigned();

→NULL — read-only transaction, no ID assigned yet

sql

BEGIN; UPDATE orders SET status = 'processed' WHERE id = 1; SELECT txid_current_if_assigned();

→1234568 — transaction got an ID when the write happened

sql

SELECT txid_current_if_assigned() IS NOT NULL AS is_write_transaction;

→false for read-only, true for transactions with writes

sql

-- PG13+ equivalent:
SELECT pg_current_xact_id_if_assigned();

→NULL or xid8

⚠Anti-Pattern— Calling txid_current() when you only want a non-NULL ID for write transactions

`txid_current()` always assigns a transaction ID, even in read-only transactions. This unnecessarily advances the XID counter, consuming the limited XID space. Use `txid_current_if_assigned()` (or `pg_current_xact_id_if_assigned()` in PG13+) to avoid assigning XIDs to read-only transactions.

✓ Instead: SELECT pg_current_xact_id_if_assigned(); -- returns NULL for read-only, no XID consumed

A NULL return means the transaction hasn't assigned an XID yet (no writes). This is useful in trigger functions or audit helpers that want to record a transaction ID only when the transaction is actually modifying data.

example

SELECT COALESCE(pg_current_xact_id_if_assigned()::text, 'read-only') AS xact_status;

→read-only (for SELECT-only transactions)

Common Gotchas

⚠

now() is frozen within a transaction — use clock_timestamp() for real wall time

now() returns the start time of the current transaction, not the current wall clock time. It doesn't advance during a long transaction.

version

PG 7.4+→ text

Returns a string describing the PostgreSQL server version and build information.

Developer

Signature

version ( ) → text

Examples

sql

SELECT version();

→PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc...

sql

SELECT regexp_match(version(), 'PostgreSQL (\d+\.\d+)')[1];

→18.0

Numeric version comparison — never parse version() string

sql

SELECT current_setting('server_version_num')::integer >= 160000 AS is_pg16_or_later;

→true

Extract just the version number portion

sql

SELECT split_part(version(), ' ', 2) AS pg_version;

→'16.2'

⚠Anti-Pattern— Parsing version() string to extract major version number

The version() string format ("PostgreSQL 16.2 on ...") is locale-sensitive and can change. String parsing is fragile.

✓ Instead: Use current_setting('server_version_num')::integer for numeric comparisons, e.g. >= 160000 for PG 16+

For programmatic version checks, use `current_setting('server_version_num')::integer` to get a numeric version (e.g., 180000 for 18.0.0). This is easier to compare than parsing the version() string.

example

SELECT current_setting('server_version_num')::integer >= 160000 AS is_pg16_or_later;

→true on PostgreSQL 16+