PostgreSQL Commands

Connecting

Default port is 5432. Build a URL for your host with the PostgreSQL connection string generator.

Example: connect over verified TLS with the password stored in ~/.pgpass instead of your shell history.

echo 'db.example.com:5432:shop:app:s3cret' >> ~/.pgpass
chmod 600 ~/.pgpass
psql "host=db.example.com dbname=shop user=app sslmode=verify-full sslrootcert=ca.pem"

psql Meta Commands

Backslash commands run in psql only, not in other clients. Start psql with -E to print the SQL behind each one.

Outside psql, SELECT datname FROM pg_database; replaces \l and SELECT tablename FROM pg_tables WHERE schemaname = 'public'; replaces \dt.

Example: a psql session that finds the orders table, checks its size and watches new rows arrive.

\c shop
\dt+ orders
\d orders
\timing on
SELECT count(*) FROM orders WHERE created_at > now() - interval '1 hour' \watch 5

Databases and Schemas

Example: make a scratch copy of the shop database for a risky migration, then drop it.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'shop' AND pid <> pg_backend_pid();       -- template must be idle
CREATE DATABASE shop_migration_test TEMPLATE shop OWNER app;
DROP DATABASE shop_migration_test WITH (FORCE);           -- when you are done

Users, Roles and Grants

A user is a role with LOGIN. Since PostgreSQL 15, ordinary users cannot create tables in the public schema until you grant CREATE.

Example: a read-only reporting user for the shop database that also sees tables added later.

CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
CREATE USER metabase WITH PASSWORD 'change-me' IN ROLE readonly;

Tables

Example: the orders table used on this page, with an identity key, a foreign key and a check.

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total      numeric(10,2) NOT NULL CHECK (total >= 0),
  status     text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now()
);

Queries

Format long queries with the PostgreSQL formatter.

Example: upsert a user, then report what each user has spent with a CTE.

INSERT INTO users (email, name, plan) VALUES ('[email protected]', 'Ada King', 'team')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, plan = EXCLUDED.plan
RETURNING id, plan;

WITH paid AS (
  SELECT user_id, sum(total) AS spent FROM orders WHERE status = 'paid' GROUP BY user_id
)
SELECT u.email, coalesce(p.spent, 0) AS spent
FROM users u LEFT JOIN paid p ON p.user_id = u.id
ORDER BY spent DESC;

Import and Export

\copy reads and writes files on your machine. Plain COPY uses files on the database server and needs superuser or the pg_write_server_files role.

In 1bench: the PostgreSQL GUI exports a whole database, one schema or one table to a .sql file with structure, data and sequence values, read inside one read-only transaction. It does not need pg_dump installed.

Example: back up the shop database every night and restore it into a fresh database to prove the backup works.

pg_dump -Fc -d shop -f "shop-$(date +%F).dump"
createdb -O app shop_restore_check
pg_restore -d shop_restore_check --no-owner "shop-$(date +%F).dump"
psql -d shop_restore_check -c 'SELECT count(*) FROM orders'

Indexes and EXPLAIN

Since PostgreSQL 18, EXPLAIN ANALYZE prints buffer counts without asking.

Example: check that the latest-orders query uses an index, and time a delete without keeping it.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20;
-- Index Scan using orders_user_created_idx on orders ... (actual time=0.009..0.010 rows=20.00 loops=1)

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'refunded';   -- really deletes
ROLLBACK;                                                       -- so put it back

Sessions and Locks

Ctrl+C in psql cancels the running query, the same as pg_cancel_backend.

In 1bench: the PostgreSQL GUI server overview shows connections against max_connections split into active and idle, the cache hit rate, uptime, and the largest databases with their size and connection count.

Example: find the session blocking an update and cancel its query, killing the session only if that fails.

SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;   -- 26077 | {26074} | UPDATE users ...

SELECT pg_cancel_backend(26074);
SELECT pg_terminate_backend(26074);              -- if it is idle in transaction

Server Admin

pg_stat_statements also needs shared_preload_libraries = 'pg_stat_statements' in postgresql.conf and a restart.

Example: raise work_mem for the whole server without a restart, then confirm it from a new session.

psql -U postgres -c "ALTER SYSTEM SET work_mem = '64MB'" -c "SELECT pg_reload_conf()"
psql -U postgres -c 'SHOW work_mem'   # 64MB

Dates, JSONB and Strings

The common ones. A full date functions sheet and a JSONB sheet are coming. Escape quotes in values with the PostgreSQL escape tool.

Example: daily signup counts for the last week from the events table, with plan pulled from JSONB.

SELECT d::date AS day, e.payload->>'plan' AS plan, count(e.id) AS signups
FROM generate_series(current_date - 6, current_date, interval '1 day') d
LEFT JOIN events e ON e.created_at::date = d::date AND e.type = 'signup'
GROUP BY 1, 2
ORDER BY 1;

Neon, Supabase, RDS and YugabyteDB

All four speak the Postgres protocol, so psql, pg_dump and every command above work. These rows differ.

RDS and Aurora have no superuser. The master user gets rds_superuser, and server settings live in parameter groups, so ALTER SYSTEM is refused.

In 1bench: the Amazon RDS GUI signs a fresh IAM token for every new connection and ships the RDS CA bundle, so verify-full works without downloading global-bundle.pem or exporting a new PGPASSWORD every 15 minutes.

Example: connect to RDS PostgreSQL with IAM auth and a verified TLS connection.

curl -sO https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
export PGPASSWORD="$(aws rds generate-db-auth-token \
  --hostname shop.abc123.us-east-1.rds.amazonaws.com --port 5432 \
  --region us-east-1 --username app)"
psql "host=shop.abc123.us-east-1.rds.amazonaws.com dbname=shop user=app sslmode=verify-full sslrootcert=global-bundle.pem"

Gotchas

• Unquoted names fold to lowercase. A table created as "Users" must be quoted in every query, and SELECT * FROM Users looks for users.
• GRANT ALL ON DATABASE shop does not grant table access. It covers CONNECT, CREATE and TEMP only, so users still get "permission denied for table" (error 42501) until you grant on the schema and tables.
• ALTER DEFAULT PRIVILEGES only covers tables created later by the role it names (or by you, without FOR ROLE). Run it as, or for, the role that runs your migrations.
• ALTER COLUMN ... TYPE fails with "cannot alter type of a column used by a view or rule". Drop and recreate the view in the same transaction.
• EXPLAIN ANALYZE executes the statement. On an UPDATE or DELETE, wrap it in BEGIN and ROLLBACK.
• On macOS, pg_dump -j and pg_restore -j can fail with "a worker process died unexpectedly" when the host is a name like localhost. It showed up in testing with the EDB 18.6 binaries. Setting PGGSSENCMODE=disable fixed it, and so did connecting to 127.0.0.1.