PostgreSQL 速查表：80+ 命令与函数带真实生产坑（psql、JSONB、CTE、窗口函数、索引）

\l

\l+

\l app_*  -- pattern match

\c production

\c app_db readonly_user

\c - postgres  -- same db, new user

\dt

\dt+

\dt public.user*

\dt myschema.*

\d users

\d+ users  -- with storage details

\d users_id_seq  -- describe a sequence

\du

\du+  -- include description

\du app_*

\q

\i schema.sql

\ir relative/migration.sql  -- relative to current file

\set ON_ERROR_STOP on
\i deploy.sql

\timing on

\timing off

\timing  -- toggle

\x

\x on

\x auto

\! ls -la

\! pwd

\!  -- enter subshell, type exit to return

\conninfo

\copy users FROM 'users.csv' WITH CSV HEADER

\copy (SELECT * FROM orders WHERE status='paid') TO 'paid.csv' WITH CSV HEADER

\dn

\df

\df+ my_func

\df *json*

\e

\ef my_function

\ev my_view  -- edit a view definition

SELECT count(*) FROM pg_stat_activity;
\watch 5

SELECT now(), pg_size_pretty(pg_database_size('app'));
\watch 10

\set ON_ERROR_STOP on

\pset null '∅'  -- show NULL distinctly

\pset format wrapped

SELECT * FROM users WHERE id = 1 \gx

SELECT * FROM orders \g /tmp/orders.txt

\dx

\di+ users*

\dp orders  -- who can do what on orders

SELECT format('REINDEX INDEX CONCURRENTLY %I;', indexname)
  FROM pg_indexes WHERE schemaname = 'public'
\gexec

\errverbose

CREATE TABLE users (id bigserial, name text, email text);

ALTER TABLE users ALTER COLUMN bio TYPE text;

age smallint

user_id bigint

CREATE TABLE orders (id bigserial PRIMARY KEY);

id bigserial PRIMARY KEY

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY  -- SQL standard

id uuid DEFAULT gen_random_uuid() PRIMARY KEY

SELECT gen_random_uuid();

created_at timestamptz NOT NULL DEFAULT now()

SELECT created_at AT TIME ZONE 'Asia/Shanghai' FROM orders;

SELECT now() + interval '1 hour';

SELECT now() - interval '7 days';

DELETE FROM sessions WHERE created_at < now() - interval '30 days';

config jsonb NOT NULL DEFAULT '{}'

INSERT INTO events (payload) VALUES ('{"type":"click","x":42}'::jsonb);

tags text[] DEFAULT '{}'

SELECT * FROM posts WHERE tags @> ARRAY['rust'];

UPDATE posts SET tags = array_append(tags, 'wasm') WHERE id = 1;

CREATE TYPE order_status AS ENUM ('pending','paid','shipped','refunded');

ALTER TYPE order_status ADD VALUE 'cancelled' AFTER 'pending';

price numeric(12, 2) NOT NULL

SELECT 0.1::numeric + 0.2::numeric;  -- exactly 0.3

SELECT round(amount, 2) FROM invoices;

is_active boolean NOT NULL DEFAULT true

SELECT * FROM users WHERE is_active IS NOT FALSE;

birth_date date

SELECT current_date, current_time;

SELECT date_trunc('month', created_at)::date FROM orders;

SELECT '[2026-05-01,2026-06-01)'::daterange @> '2026-05-15'::date;

CREATE TABLE bookings (
  room_id int,
  during tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

client_ip inet

SELECT * FROM logs WHERE client_ip <<= '10.0.0.0/8';  -- inside subnet

SELECT '192.168.1.5/24'::inet + 10;

SELECT digest('hello', 'sha256');  -- needs pgcrypto, returns bytea

SELECT encode(digest('hello', 'sha256'), 'hex');

SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox');

CREATE INDEX docs_fts ON docs USING GIN (to_tsvector('english', body));

SELECT * FROM docs WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'brown fox');

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text UNIQUE NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (id bigserial PRIMARY KEY, action text);

ALTER TABLE users ADD COLUMN avatar_url text;

ALTER TABLE users DROP COLUMN deprecated_field;

ALTER TABLE users RENAME COLUMN nickname TO display_name;

ALTER TABLE users ADD CONSTRAINT users_email_lower CHECK (email = lower(email)) NOT VALID;

DROP TABLE IF EXISTS old_users;

DROP TABLE users CASCADE;  -- careful

CREATE INDEX CONCURRENTLY users_email_idx ON users (email);

CREATE UNIQUE INDEX CONCURRENTLY orders_uuid_uidx ON orders (uuid);

CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) d, sum(amount) FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

ALTER TABLE products
  ADD COLUMN search_text text
  GENERATED ALWAYS AS (lower(name) || ' ' || lower(coalesce(brand, ''))) STORED;

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX orders_user_id_idx ON orders (user_id);  -- index the FK!

ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT amount_positive;

COMMENT ON COLUMN users.status IS 'one of: pending, active, banned (see app enum)';

COMMENT ON TABLE audit_log IS 'append-only; never UPDATE rows here';

CREATE SEQUENCE invoice_no_seq START 1000 INCREMENT 1;

SELECT nextval('invoice_no_seq');

CREATE SEQUENCE s OWNED BY mytable.id;

CREATE SCHEMA IF NOT EXISTS analytics AUTHORIZATION analyst;

SET search_path TO analytics, public;

CREATE TABLE analytics.events (id bigserial PRIMARY KEY);

ALTER TABLE users RENAME TO app_users;

ALTER INDEX users_email_idx RENAME TO app_users_email_idx;

ALTER TABLE events SET SCHEMA analytics;

ALTER TABLE users ADD CONSTRAINT users_email_uq UNIQUE (email);

CREATE UNIQUE INDEX ON tags (lower(name)) NULLS NOT DISTINCT;  -- PG 15+

ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room WITH =, during WITH &&);

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada');

INSERT INTO users (email, name) VALUES ('a@x.com','Ada'), ('b@x.com','Bob'), ('c@x.com','Cy');

INSERT INTO users (email) VALUES ('a@x.com') RETURNING id, created_at;

INSERT INTO orders (user_id, amount) SELECT id, 0 FROM users WHERE created_at > now() - interval '1 day' RETURNING *;

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

INSERT INTO event_dedupe (event_id) VALUES ('e123')
ON CONFLICT DO NOTHING;

UPDATE users SET name = 'Ada Lovelace' WHERE id = 1 RETURNING *;

UPDATE orders o SET status = 'paid' FROM payments p
WHERE o.id = p.order_id AND p.confirmed_at IS NOT NULL
RETURNING o.id;

DELETE FROM sessions WHERE expires_at < now() RETURNING id;

WITH victims AS (
  SELECT id FROM sessions WHERE expires_at < now() ORDER BY id LIMIT 10000
)
DELETE FROM sessions WHERE id IN (SELECT id FROM victims) RETURNING id;

TRUNCATE TABLE staging_imports;

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

INSERT INTO users_archive (id, email, archived_at)
  SELECT id, email, now() FROM users WHERE deleted_at < now() - interval '90 days';

COPY users (email, name) FROM '/data/users.csv' WITH (FORMAT csv, HEADER true);

COPY (SELECT * FROM orders WHERE created_at >= '2026-01-01') TO STDOUT WITH (FORMAT csv, HEADER true);

MERGE INTO inventory i
USING shipments s ON i.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = i.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

SELECT id FROM jobs WHERE status = 'queued'
  ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

SELECT region, product, sum(amount)
  FROM sales GROUP BY ROLLUP (region, product);

SELECT region, product, sum(amount), grouping(region, product) AS g
  FROM sales GROUP BY GROUPING SETS ((region), (product), ());

SELECT DISTINCT ON (user_id) user_id, amount, created_at
  FROM orders ORDER BY user_id, created_at DESC;

SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 40;  -- page 3

SELECT * FROM posts WHERE id > 1234 ORDER BY id LIMIT 20;  -- keyset, fast

SELECT data->'user'->>'email' FROM events;

SELECT * FROM events WHERE (data->>'count')::int > 5;

SELECT data #> '{user,address,city}' FROM events;

SELECT data #>> '{user,name}' FROM events;

SELECT * FROM events WHERE data @> '{"type":"click"}';

SELECT * FROM users WHERE prefs @> '{"theme":"dark","lang":"zh"}';

SELECT * FROM events WHERE data ? 'user_id';

SELECT * FROM events WHERE data ?| ARRAY['email','phone'];

SELECT * FROM events WHERE data ?& ARRAY['ip','ua'];

SELECT jsonb_build_object('id', id, 'email', email, 'created', created_at) FROM users;

UPDATE users SET prefs = jsonb_set(prefs, '{theme}', '"dark"') WHERE id = 1;

UPDATE users SET prefs = jsonb_set(coalesce(prefs, '{}'::jsonb), '{lang}', '"zh"', true);

SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 100)') FROM orders;

SELECT * FROM orders WHERE jsonb_path_exists(data, '$.items[*] ? (@.sku == "X1")');

SELECT e.value->>'sku' AS sku
  FROM orders, jsonb_array_elements(data->'items') AS e;

SELECT jsonb_array_elements_text('["a","b","c"]'::jsonb);

SELECT key, value FROM events, jsonb_each(data) WHERE id = 1;

SELECT jsonb_object_keys(prefs) FROM users WHERE id = 1;

SELECT u.id, jsonb_agg(o.* ORDER BY o.created_at) AS orders
  FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;

SELECT jsonb_object_agg(code, name) FROM countries;

SELECT '{"a":1,"b":2}'::jsonb || '{"b":9,"c":3}'::jsonb;  -- {a:1,b:9,c:3}

UPDATE users SET prefs = prefs - 'legacy_flag' WHERE id = 1;

SELECT data #- '{user,temp}' FROM events;

SELECT to_jsonb(u) FROM users u WHERE id = 1;

SELECT to_jsonb(array_agg(name)) FROM tags;

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT user_id, count(*) FROM recent GROUP BY user_id;

WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name, org.depth + 1
    FROM employees e JOIN org ON e.manager_id = org.id
    WHERE org.depth < 100
)
SELECT * FROM org;

WITH moved AS (
  DELETE FROM events WHERE created_at < now() - interval '30 days' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;

WITH batch AS (
  SELECT id FROM events
    WHERE processed = false ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE events SET processed = true
  WHERE id IN (SELECT id FROM batch) RETURNING id;

WITH heavy AS MATERIALIZED (
  SELECT * FROM big_join_view
)
SELECT * FROM heavy WHERE x = 1
UNION ALL
SELECT * FROM heavy WHERE y = 2;

WITH RECURSIVE g AS (
  SELECT id, parent_id, ARRAY[id] AS path FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, g.path || n.id
    FROM nodes n JOIN g ON n.parent_id = g.id
    WHERE NOT n.id = ANY(g.path)
)
SELECT * FROM g;

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) t WHERE rn <= 3;

SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM players;

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM players;

SELECT day, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS day_over_day
  FROM daily_revenue;

SELECT user_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS quartile FROM users;

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
  FROM daily_revenue;

SELECT user_id,
       FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
  FROM orders;

SELECT user_id,
       SUM(amount) OVER w,
       AVG(amount) OVER w,
       COUNT(*)   OVER w
  FROM orders
  WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

SELECT name, score, round((percent_rank() OVER (ORDER BY score))::numeric, 3)
  FROM students;

SELECT
  percentile_cont(0.5)  WITHIN GROUP (ORDER BY latency_ms) AS p50,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99
FROM requests;

SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS len
FROM (
  SELECT user_id, day,
    day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM active_days
) t
GROUP BY user_id, grp;

SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'paid')     AS paid,
  count(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;

CREATE INDEX users_email_idx ON users (email);

CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);

CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);  -- needs pg_trgm

CREATE INDEX locations_geom_gist ON locations USING GiST (geom);

CREATE INDEX bookings_period_gist ON bookings USING GiST (period);  -- daterange

CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

CREATE INDEX users_active_email_idx ON users (email) WHERE deleted_at IS NULL;

CREATE INDEX users_email_lower_idx ON users (lower(email));

CREATE INDEX events_date_idx ON events (date_trunc('day', created_at));

CREATE INDEX users_email_inc_idx ON users (email) INCLUDE (name, created_at);

CREATE INDEX events_created_brin ON events USING BRIN (created_at);

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = $1;

BEGIN;
EXPLAIN (ANALYZE) UPDATE users SET name = 'x' WHERE id = 1;
ROLLBACK;

CREATE INDEX sessions_token_hash ON sessions USING hash (token);

REINDEX INDEX CONCURRENTLY users_email_idx;

REINDEX TABLE CONCURRENTLY orders;

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

EXPLAIN (GENERIC_PLAN) SELECT * FROM users WHERE email = $1;

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 0) AS total_ms, query
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

SELECT pg_stat_statements_reset();  -- zero the counters before a test

CREATE TABLE events (id bigserial, created_at timestamptz NOT NULL, payload jsonb)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE orders (id bigserial, region text, amount numeric)
  PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east','us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west','eu-central');

CREATE TABLE messages (id bigint, user_id bigint, body text)
  PARTITION BY HASH (user_id);

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 1);

ALTER TABLE events ATTACH PARTITION events_2026_06 FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;

CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE TABLE metrics (tenant_id int, ts timestamptz, val numeric)
  PARTITION BY RANGE (ts);

CREATE TABLE metrics_2026_05 PARTITION OF metrics
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01')
  PARTITION BY HASH (tenant_id);

EXPLAIN SELECT * FROM events WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16';

pg_dump -Fc -f app.dump app

pg_dump -Fd -j 4 -f app_dir app  # 4-way parallel dump

pg_dump --schema-only -f schema.sql app

pg_restore -d app_new -j 4 app.dump

pg_restore -d app -t users app.dump

pg_restore --schema-only -d app schema.dump

pg_basebackup -h primary.db -D /var/lib/postgresql/replica -U replicator -R -P

SELECT pg_create_physical_replication_slot('replica1');

SELECT slot_name, active, confirmed_flush_lsn FROM pg_replication_slots;

CREATE PUBLICATION mypub FOR TABLE users, orders;

CREATE SUBSCRIPTION mysub CONNECTION 'host=src dbname=app user=repl' PUBLICATION mypub;

pg_dumpall --globals-only -f globals.sql

pg_dumpall -f full_cluster.sql

# in postgresql.conf
archive_mode = on
archive_command = 'test ! -f /arch/%f && cp %p /arch/%f'

# in recovery target config
recovery_target_time = '2026-05-29 14:30:00+00'

SET LOCAL synchronous_commit = off;  -- this transaction only

SELECT application_name, state, sync_state FROM pg_stat_replication;

SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
  FROM pg_stat_replication;

SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;  -- run on the standby

CREATE ROLE app_readonly NOLOGIN;

CREATE USER app_service WITH PASSWORD 'xxx';

GRANT app_readonly TO app_service;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

# TYPE  DATABASE  USER  ADDRESS         METHOD

host    all       all   10.0.0.0/8      scram-sha-256

hostssl app       app   0.0.0.0/0       scram-sha-256

local   all       all                   peer

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

ALTER TABLE notes FORCE ROW LEVEL SECURITY;

CREATE POLICY my_notes ON notes
  USING (user_id = current_setting('app.user_id')::bigint);

SET ROLE app_readonly;

RESET ROLE;

SET SESSION AUTHORIZATION 'reporting';

ALTER ROLE app_service CONNECTION LIMIT 50;

ALTER ROLE app_service SET statement_timeout = '30s';

ALTER ROLE admin SET idle_in_transaction_session_timeout = '60s';

GRANT pg_read_all_data TO analyst;

GRANT pg_monitor TO observability;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- needs shared_preload_libraries

SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC LIMIT 20;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);

SELECT * FROM users WHERE name % 'Adda';  -- fuzzy match

CREATE EXTENSION IF NOT EXISTS postgis;

SELECT name FROM places
  WHERE ST_DWithin(geog, ST_MakePoint(-122.4, 37.8)::geography, 1000);  -- within 1km

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v1();

SELECT uuid_generate_v5(uuid_ns_url(), 'https://x.com/user/42');

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO users (password) VALUES (crypt('hunter2', gen_salt('bf')));

SELECT * FROM users WHERE password = crypt('hunter2', password);

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (email citext UNIQUE);

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (id bigserial, embedding vector(1536));

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 5;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER src FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'app');

IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER src INTO remote;

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD CONSTRAINT no_double_book
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

SELECT name, default_version, installed_version FROM pg_available_extensions WHERE installed_version IS NOT NULL;

ALTER EXTENSION postgis UPDATE;

DROP EXTENSION IF EXISTS hstore CASCADE;

SELECT * FROM users WHERE deleted_at IS NULL;

SELECT * FROM users WHERE id IS DISTINCT FROM other_id;  -- treats NULL as a value

SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM refunds WHERE refunds.order_id = orders.id);

VACUUM ANALYZE users;

VACUUM FULL users;  -- rewrites table, exclusive lock — avoid in production

SELECT pid, age(now(), xact_start) AS age, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;

SET lock_timeout = '5s';  -- never block forever

ALTER TABLE users ADD COLUMN x text;  -- safe, metadata only

ALTER TABLE users ADD COLUMN id_v2 uuid DEFAULT gen_random_uuid();  -- REWRITES TABLE — danger

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

SELECT last_value, is_called FROM users_id_seq;

SELECT slot_name, active, restart_lsn, pg_size_pretty(pg_current_wal_lsn() - restart_lsn) AS lag FROM pg_replication_slots;

SELECT pg_drop_replication_slot('dead_replica');

SHOW timezone;

SET TIME ZONE 'Asia/Shanghai';

SELECT created_at AT TIME ZONE 'UTC' FROM orders;  -- explicit conversion

SHOW search_path;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE FUNCTION sensitive() RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER SET search_path = pg_catalog, pg_temp
AS $$ BEGIN /* ... */ END $$;

-- bad: '5' is text, may skip a bigint index
SELECT * FROM orders WHERE id = '5';

SELECT * FROM orders WHERE id = 5;  -- typed correctly

UPDATE accounts SET bal = bal - 10 WHERE id = LEAST(1, 2);
UPDATE accounts SET bal = bal + 10 WHERE id = GREATEST(1, 2);

SELECT reltuples::bigint AS approx_rows FROM pg_class WHERE relname = 'events';

SELECT count(*) FROM orders WHERE status = 'open';  -- ok if status is indexed & selective

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

SHOW max_connections;

-- may seq scan:
SELECT * FROM users WHERE email = $1 OR phone = $2;

SELECT * FROM users WHERE email = $1
UNION
SELECT * FROM users WHERE phone = $2;