Lệnh psql
Meta-command chạy bên trong psql (chương trình máy khách terminal của PostgreSQL), không chạy trên máy chủ. Chúng bắt đầu bằng dấu gạch chéo ngược và không cần dấu chấm phẩy.
| Tác dụng | Lệnh |
|---|
| Liệt kê cơ sở dữ liệu | \l |
| Kết nối tới cơ sở dữ liệu khác | \c shop |
| Liệt kê bảng | \dt |
| Liệt kê bảng trong mọi schema | \dt *.* |
| Mô tả bảng (cột, chỉ mục, khóa) | \d users |
| Mô tả kèm thông tin lưu trữ và chú thích | \d+ users |
| Liệt kê schema | \dn |
| Liệt kê view | \dv |
| Liệt kê chỉ mục | \di |
| Liệt kê hàm | \df |
| Liệt kê role (người dùng) | \du |
| Xem kết nối hiện tại | \conninfo |
| Chạy SQL từ một tệp | \i setup.sql |
| Bật hoặc tắt đo thời gian truy vấn | \timing |
| Hiển thị mở rộng (mỗi cột một dòng) | \x |
| Sửa truy vấn gần nhất trong trình soạn thảo | \e |
| Trợ giúp cú pháp SQL | \h CREATE TABLE |
| Liệt kê mọi meta-command | \? |
| Thoát psql | \q |
Kết nối từ terminal
| Tác dụng | Lệnh |
|---|
| Kết nối tới cơ sở dữ liệu với một người dùng | psql -U postgres -d shop |
| Kết nối tới host và cổng từ xa | psql -h db.example.com -p 5432 -U app -d shop |
| Kết nối bằng URL kết nối | psql postgresql://app:secret@localhost:5432/shop |
| Chạy một truy vấn rồi thoát | psql -d shop -c "SELECT count(*) FROM users;" |
| Chạy một tệp SQL | psql -d shop -f setup.sql |
| Tạo cơ sở dữ liệu từ shell | createdb shop |
| Xóa cơ sở dữ liệu từ shell | dropdb shop |
Cơ sở dữ liệu và schema
| Tác dụng | SQL |
|---|
| Tạo cơ sở dữ liệu | CREATE DATABASE shop; |
| Xóa cơ sở dữ liệu | DROP DATABASE shop; |
| Xem cơ sở dữ liệu hiện tại | SELECT current_database(); |
| Tạo schema | CREATE SCHEMA sales; |
| Xem thứ tự tìm kiếm schema | SHOW search_path; |
| Tìm trong một schema trước | SET search_path TO sales, public; |
| Xem phiên bản máy chủ | SELECT version(); |
Bảng và kiểu dữ liệu
| Tác dụng | SQL |
|---|
| Tạo bảng có khóa identity | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Thêm cột | ALTER TABLE users ADD COLUMN phone text; |
| Đổi tên cột | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Đổi kiểu của cột | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Đặt giá trị mặc định | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Xóa cột | ALTER TABLE users DROP COLUMN tags; |
| Làm trống bảng và đặt lại identity | TRUNCATE orders, users RESTART IDENTITY; |
| Xóa bảng nếu tồn tại | DROP TABLE IF EXISTS products; |
| Số thập phân chính xác cho tiền | numeric(10,2) |
| Ngày giờ có múi giờ | timestamptz |
| UUID ngẫu nhiên | SELECT gen_random_uuid(); |
| Kiểu enum | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Ràng buộc và khóa
| Tác dụng | SQL |
|---|
| Thêm khóa chính | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Khóa ngoại xóa luôn các hàng con | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Thêm ràng buộc check | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Unique coi các NULL là bằng nhau (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Bắt buộc một cột phải có giá trị | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Xóa ràng buộc | ALTER TABLE users DROP CONSTRAINT users_email_key; |
Truy vấn (phần riêng của PostgreSQL)
SELECT, WHERE và JOIN chuẩn có trong cheat sheet SQL; đây là các dạng riêng của PostgreSQL.
| Tác dụng | SQL |
|---|
| So khớp không phân biệt hoa thường | SELECT * FROM users WHERE name ILIKE 'a%'; |
| So khớp biểu thức chính quy | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| Hàng đầu tiên của mỗi nhóm | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| Limit và offset | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Giới hạn số hàng theo chuẩn | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Ép kiểu bằng :: | SELECT '42'::integer + 1; |
| Giá trị thay thế cho NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| So sánh coi các NULL là bằng nhau | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Tạo dãy số | SELECT * FROM generate_series(1, 5); |
| Gộp thành một chuỗi | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Hàm tổng hợp có điều kiện | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Insert, update, delete và upsert
| Tác dụng | SQL |
|---|
| Insert và lấy lại id mới | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Insert nhiều hàng | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (insert hoặc update) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Insert, bỏ qua bản trùng | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Update và xem các hàng đã thay đổi | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Update từ một bảng khác | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Delete và xem những gì đã xóa | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Delete dựa trên một bảng khác | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Sao chép hàng sang bảng mới | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| Tác dụng | SQL |
|---|
| Lấy một trường dưới dạng JSON | SELECT profile -> 'plan' FROM users; |
| Lấy một trường dưới dạng văn bản | SELECT profile ->> 'plan' FROM users; |
| Lấy trường lồng nhau dưới dạng văn bản | SELECT profile #>> '{address,city}' FROM users; |
| Các hàng có JSON chứa một giá trị | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Các hàng có một khóa | SELECT * FROM users WHERE profile ? 'city'; |
| Đặt giá trị cho khóa | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Gộp đối tượng | UPDATE users SET profile = profile || '{"beta": true}'; |
| Xóa một khóa | UPDATE users SET profile = profile - 'city'; |
| Tạo JSON từ các cột | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Các hàng dưới dạng mảng JSON | SELECT jsonb_agg(users) FROM users; |
| Chỉ mục cho tra cứu @> và ? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Mảng
| Tác dụng | SQL |
|---|
| Giá trị mảng viết trực tiếp | SELECT ARRAY[1, 2, 3]; |
| Các hàng có mảng chứa một giá trị | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Các hàng có mảng chứa tất cả giá trị | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| Mỗi phần tử một hàng | SELECT name, unnest(tags) FROM users; |
| Thêm phần tử vào cuối | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Độ dài mảng | SELECT name, cardinality(tags) FROM users; |
Ngày và giờ
| Tác dụng | SQL |
|---|
| Bây giờ, có múi giờ | SELECT now(); |
| Hôm nay | SELECT current_date; |
| Cộng một khoảng thời gian | SELECT current_date + INTERVAL '7 days'; |
| Các hàng trong 30 ngày gần nhất | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Làm tròn xuống theo tháng | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Trích một phần | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Khoảng cách giữa hai ngày (số ngày) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Khoảng thời gian giữa hai ngày | SELECT name, age(current_date, hired_on) FROM employees; |
| Định dạng ngày thành văn bản | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Chuyển văn bản thành ngày | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Chuyển sang múi giờ khác | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Hàm xử lý chuỗi
| Tác dụng | SQL |
|---|
| Nối chuỗi (có NULL thì ra NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Nối chuỗi, bỏ qua NULL | SELECT concat_ws(' ', name, email) FROM users; |
| Chữ hoa và chữ thường | SELECT upper(name), lower(name) FROM users; |
| Độ dài | SELECT name, length(name) FROM users; |
| Một phần của chuỗi | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Thay thế | SELECT replace(email, '@example.com', '') FROM users; |
| Tách và lấy một phần | SELECT split_part(email, '@', 2) FROM users; |
| Thay thế bằng biểu thức chính quy | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Cắt khoảng trắng hai đầu | SELECT trim(' hi '); |
CTE và window function
| Tác dụng | SQL |
|---|
| Đặt tên cho truy vấn con bằng WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| CTE đệ quy (đếm đến 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Đánh số hàng theo nhóm | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Xếp hạng có đồng hạng | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Tổng lũy kế | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Giá trị của hàng trước | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Join mỗi hàng với truy vấn con của riêng nó | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Chỉ mục
| Tác dụng | SQL |
|---|
| Chỉ mục B-tree (mặc định) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Chỉ mục unique | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Chỉ mục nhiều cột | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Chỉ mục một phần | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Chỉ mục trên biểu thức | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Chỉ mục bao phủ (quét chỉ dùng chỉ mục) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Tạo mà không chặn thao tác ghi | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Xóa chỉ mục | DROP INDEX IF EXISTS orders_user_idx; |
| Xem kế hoạch thực thi | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| Xem kế hoạch thực thi với thời gian thực tế | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Giao dịch và khóa
| Tác dụng | SQL |
|---|
| Bắt đầu, rồi giữ lại hoặc hoàn tác | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Hoàn tác một phần giao dịch | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Mức cô lập chặt hơn | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Khóa các hàng bạn đọc | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| Hàng đợi công việc: bỏ qua hàng đang bị khóa | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
Người dùng, role và quyền
| Tác dụng | SQL |
|---|
| Tạo role có quyền đăng nhập | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Quyền chỉ đọc trên một bảng | GRANT SELECT ON users TO app; |
| Quyền đọc mọi bảng trong một schema | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Thu hồi quyền | REVOKE SELECT ON users FROM app; |
| Đổi mật khẩu | ALTER ROLE app PASSWORD 'new-secret'; |
| Xem người dùng hiện tại | SELECT current_user; |
Bảo trì và giám sát
| Tác dụng | SQL |
|---|
| Các truy vấn đang chạy | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Hủy một truy vấn | SELECT pg_cancel_backend(12345); |
| Ngắt một kết nối | SELECT pg_terminate_backend(12345); |
| Kích thước bảng kèm chỉ mục | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Kích thước cơ sở dữ liệu | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Cập nhật thống kê cho planner | ANALYZE orders; |
| Thu hồi các hàng chết | VACUUM orders; |
| Ghi lại bảng về kích thước nhỏ nhất (sẽ khóa bảng) | VACUUM FULL orders; |
| Xem một thiết lập | SHOW work_mem; |
| Đổi thiết lập cho phiên này | SET work_mem = '64MB'; |
Sao lưu và khôi phục
Lệnh shell từ bộ công cụ máy khách của PostgreSQL.
| Tác dụng | Lệnh |
|---|
| Dump cơ sở dữ liệu ra tệp SQL | pg_dump -U postgres shop > shop.sql |
| Dump theo định dạng custom | pg_dump -Fc -U postgres shop > shop.dump |
| Dump một bảng | pg_dump -t orders shop > orders.sql |
| Khôi phục bản dump SQL | psql -d shop -f shop.sql |
| Khôi phục bản dump định dạng custom | pg_restore -d shop shop.dump |
| Dump mọi cơ sở dữ liệu và role | pg_dumpall > cluster.sql |
| Xuất kết quả truy vấn ra CSV (từ psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Nhập tệp CSV (từ psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Mọi lệnh psql và câu lệnh PostgreSQL bạn hay dùng, trong các bảng sao chép được ngay: đầu tiên là kết nối và nhóm lệnh \d, sau đó là bảng, upsert, JSONB, ngày giờ, window function, chỉ mục, role, sao lưu và bảo trì.
SQL ở đây theo cú pháp PostgreSQL 17, và phần lớn cũng chạy được trên các phiên bản cũ hơn. Các câu lệnh SQL đã được kiểm tra trên PostgreSQL 17; muốn thử câu nào, chỉ cần dán vào playground PostgreSQL, nơi có sẵn các bảng mẫu.