PostgreSQL System Information Functions

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.

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

`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.

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

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.

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

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

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

`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.

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;

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

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

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.

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;

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.

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

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

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

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`.

SELECT current_schema(), current_schemas(true);

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

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

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.

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