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*

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';

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;

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;

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")');

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;

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);

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 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;

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;

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);

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);

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 $$;