Essential psql Commands
psql, PostgreSQL's interactive terminal, has backslash meta-commands that are distinct from SQL: \l lists databases, \c switches the connected database, \dt lists tables, \d tablename describes a table's columns and indexes, \du lists roles, and \x toggles expanded output for wide result sets that would otherwise wrap awkwardly across a terminal. Knowing these shortcuts turns psql from a plain SQL prompt into a fast exploratory tool, especially \d+ and \di+ which add size and description columns useful when auditing a schema you didn't design.
Cricket analogy: A commentator's quick-reference cue cards for player stats, team lineups, and ground records let them answer any question instantly during a broadcast, just as psql's backslash commands let a DBA instantly inspect schema without writing SQL from scratch.
Common SQL Patterns
A handful of SQL patterns cover most day-to-day PostgreSQL work: UPSERT via INSERT ... ON CONFLICT DO UPDATE for idempotent writes, window functions like ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) for ranking within groups, and RETURNING to get back the row(s) affected by an INSERT/UPDATE/DELETE without a separate SELECT. Common Table Expressions (WITH queries) make multi-step logic readable, and since PostgreSQL 12 they can be inlined by the planner (unless marked MATERIALIZED), so they no longer carry an automatic optimization fence the way older versions did.
Cricket analogy: A team's go-to set plays for common match situations, like a rehearsed run-chase strategy for the last over, mirror having a handful of well-practiced SQL patterns ready for common data problems.
-- Idempotent upsert
INSERT INTO inventory (sku, qty)
VALUES ('SKU-100', 5)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty
RETURNING *;
-- Rank rows within groups
SELECT customer_id, order_id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank
FROM orders;
-- Readable multi-step logic with a CTE
WITH recent_orders AS (
SELECT * FROM orders WHERE created_at > now() - interval '30 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;Since PostgreSQL 12, CTEs are inlined by the planner by default (no optimization fence) unless explicitly marked WITH x AS MATERIALIZED (...), so relying on a CTE to force materialization now requires that keyword explicitly.
Handy Catalog and Monitoring Queries
The system catalog views pg_stat_activity (current connections and running queries), pg_stat_user_tables (per-table read/write and vacuum activity), and pg_locks (current lock holders and waiters) are the first places to look when something feels wrong, and pg_stat_statements (an extension that must be enabled) aggregates query execution statistics across all calls, making it the fastest way to find your database's actual top time-consuming queries rather than guessing.
Cricket analogy: A team's live match dashboard showing every player's current stats, strike rate, and field position in real time mirrors pg_stat_activity and pg_locks showing every connection's live state.
pg_stat_statements must be added to shared_preload_libraries and the server restarted before the extension can be created — it cannot be enabled with just CREATE EXTENSION alone if the library isn't preloaded.
- \l, \c, \dt, \d, \du, and \x are the most-used psql backslash meta-commands for schema exploration.
- INSERT ... ON CONFLICT DO UPDATE provides idempotent upserts in a single statement.
- RETURNING avoids a separate SELECT after INSERT/UPDATE/DELETE by returning affected rows directly.
- Window functions like ROW_NUMBER() OVER (PARTITION BY ...) rank or number rows within groups.
- Since PostgreSQL 12, CTEs are inlined by default unless marked MATERIALIZED.
- pg_stat_activity, pg_stat_user_tables, and pg_locks are the first places to check when diagnosing live issues.
- pg_stat_statements requires shared_preload_libraries configuration and a restart before it can be enabled.
Practice what you learned
1. Which psql command lists all tables in the current database?
2. What does INSERT ... ON CONFLICT DO UPDATE accomplish?
3. Since which PostgreSQL version are CTEs inlined by the planner by default (no automatic optimization fence)?
4. What must be done before pg_stat_statements can be enabled with CREATE EXTENSION?
5. What does the RETURNING clause do in an UPDATE statement?
Was this page helpful?
You May Also Like
PostgreSQL Performance Tuning
Learn how to diagnose slow queries and tune PostgreSQL configuration, indexing, and query plans for production workloads.
PostgreSQL Interview Questions
Prepare for PostgreSQL technical interviews with commonly asked questions on internals, transactions, indexing, and troubleshooting.
PostgreSQL Backup and Restore
Understand logical and physical backup strategies in PostgreSQL, including pg_dump, pg_basebackup, and point-in-time recovery.