Lệnh psql

← Biểu thức điều kiện - CASE, COALESCE, NULLIF, CAST | → Công thức thực dụng - EXPLAIN, xóa trùng, so sánh bảng

Bạn sẽ học được

Làm chủ psql — công cụ dòng lệnh chính thức. Học tốt psql giúp bạn nhanh hơn nhiều so với dùng giao diện đồ họa.

1. Kết nối

psql -U <user> -h <host> -p <port> -d <database>
 
psql -U postgres                                  # local, database mặc định
psql -U postgres -d dvdrental                     # chỉ định database
psql -h 192.168.1.50 -p 5432 -U tungdd -d app     # máy từ xa
psql "postgresql://user:pass@host:5432/db?sslmode=require"   # URI

Biến môi trường thay cho tham số:

export PGHOST=localhost
export PGUSER=postgres
export PGDATABASE=dvdrental
export PGPASSWORD=matkhau        # ⚠️ hiện trong lịch sử shell
psql                              # dùng luôn các biến trên

An toàn hơn — file ~/.pgpass (quyền chmod 600):

hostname:port:database:username:password
localhost:5432:dvdrental:postgres:matkhau
*:5432:*:tungdd:matkhau_chung

1.1. Chạy lệnh không vào shell

psql -d dvdrental -c "SELECT count(*) FROM film;"       # một lệnh
psql -d dvdrental -f script.sql                          # chạy file
psql -d dvdrental -A -t -c "SELECT count(*) FROM film;"  # chỉ giá trị, không định dạng
psql -d dvdrental --csv -c "SELECT * FROM actor LIMIT 5" # xuất CSV

Cờ	Ý nghĩa
`-c`	Chạy một lệnh rồi thoát
`-f`	Chạy file SQL
`-A`	Không căn chỉnh cột
`-t`	Không hiện tiêu đề và số dòng
`-q`	Im lặng (không in thông báo)
`-1`	Chạy cả file trong một giao dịch
`-v ON_ERROR_STOP=1`	Dừng ngay khi gặp lỗi
`-X`	Bỏ qua `~/.psqlrc`

# Chạy migration an toàn: một giao dịch, dừng khi lỗi
psql -d app -1 -v ON_ERROR_STOP=1 -f migration.sql

2. Lệnh thông tin (`\d`)

Lệnh	Hiển thị
`\l` hoặc `\list`	Danh sách database
`\c ten_db`	Chuyển database
`\dn`	Danh sách schema
`\dt`	Bảng
`\dt+`	Bảng kèm kích thước
`\dv`	View
`\dm`	Materialized view
`\di`	Index
`\ds`	Sequence
`\df`	Function
`\dT`	Kiểu dữ liệu
`\du`	Role / user
`\dp` hoặc `\z`	Quyền truy cập
`\dx`	Extension đã cài
`\dconfig`	Tham số cấu hình
`\d ten_bang`	Cấu trúc bảng
`\d+ ten_bang`	Cấu trúc chi tiết (kèm dung lượng, mô tả)

Thêm + để chi tiết hơn, thêm S để hiện cả đối tượng hệ thống:

\dt              -- bảng của người dùng
\dtS             -- gồm cả bảng hệ thống
\dt+ *film*      -- lọc theo mẫu tên
\d film*         -- mọi đối tượng bắt đầu bằng "film"

2.1. Ví dụ `\d film`

                    Table "public.film"
      Column      |           Type           | Nullable |
------------------+--------------------------+----------+
 film_id          | integer                  | not null |
 title            | character varying(255)   | not null |
 description      | text                     |          |
 release_year     | year                     |          |
 language_id      | smallint                 | not null |
 rental_duration  | smallint                 | not null |
 rental_rate      | numeric(4,2)             | not null |
 ...
Indexes:
    "film_pkey" PRIMARY KEY, btree (film_id)
    "film_fulltext_idx" gist (fulltext)
    "idx_fk_language_id" btree (language_id)
    "idx_title" btree (title)
Foreign-key constraints:
    "film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id)
Referenced by:
    TABLE "film_actor" CONSTRAINT ... FOREIGN KEY (film_id) REFERENCES film(film_id)
    ...
Triggers:
    last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE FUNCTION last_updated()

Mục Referenced by rất hữu ích — cho biết bảng nào phụ thuộc vào bảng này trước khi bạn DROP.

3. Định dạng đầu ra

Lệnh	Tác dụng
`\x`	Bật/tắt hiển thị dọc
`\x auto`	Tự động chuyển khi bảng quá rộng
`\a`	Bật/tắt căn chỉnh cột
`\t`	Bật/tắt tiêu đề và số dòng
`\pset border 2`	Kiểu viền (0, 1, 2)
`\pset null '[NULL]'`	Cách hiển thị `NULL`
`\pset format csv`	Định dạng: aligned, csv, html, latex, wrapped
`\pset pager off`	Tắt phân trang
`\pset linestyle unicode`	Viền bảng đẹp hơn

3.1. `\x` — cứu cánh cho bảng nhiều cột

\x
SELECT * FROM film WHERE film_id = 1;

-[ RECORD 1 ]----+------------------------------------------
film_id          | 1
title            | Academy Dinosaur
description      | A Epic Drama of a Feminist And a Mad ...
release_year     | 2006
language_id      | 1
rental_duration  | 6
rental_rate      | 0.99
length           | 86
replacement_cost | 20.99
rating           | PG

Dễ đọc hơn hẳn so với bảng ngang bị cắt.

4. Nhập/xuất và thực thi file

Lệnh	Tác dụng
`\i duong_dan.sql`	Chạy file SQL
`\ir duong_dan.sql`	Chạy file, đường dẫn tương đối với file hiện tại
`\o ten_file`	Ghi kết quả ra file
`\o`	Trở về màn hình
`\copy ...`	Nhập/xuất CSV phía client
`\g ten_file`	Chạy lệnh cuối, ghi ra file
`\w ten_file`	Ghi bộ đệm truy vấn ra file
`\e`	Mở trình soạn thảo để sửa truy vấn
`\ef ten_ham`	Sửa định nghĩa function
`\! lenh_shell`	Chạy lệnh shell

-- Xuất kết quả ra HTML
\pset format html
\o bao_cao.html
SELECT * FROM film LIMIT 10;
\o
\pset format aligned
 
-- Xuất CSV
\copy (SELECT * FROM film WHERE length > 150) TO 'phim.csv' CSV HEADER
 
-- Mở vim/nano để viết truy vấn dài
\e

→ Nhập và xuất dữ liệu CSV

5. Biến và tương tác

\set ten_bien 'gia_tri'
\echo :ten_bien
\unset ten_bien
\set                          -- liệt kê tất cả biến

Dùng biến trong truy vấn:

\set ma_phim 100
SELECT * FROM film WHERE film_id = :ma_phim;
 
\set ten 'Academy Dinosaur'
SELECT * FROM film WHERE title = :'ten';     -- :'x' bọc nháy đơn (giá trị)
 
\set bang 'film'
SELECT count(*) FROM :"bang";                -- :"x" bọc nháy kép (định danh)

Nhập từ bàn phím:

\prompt 'Nhập mã phim: ' ma
SELECT title FROM film WHERE film_id = :ma;

5.1. Biến hệ thống hữu ích

Biến	Tác dụng
`AUTOCOMMIT`	`on`/`off`
`ON_ERROR_STOP`	Dừng script khi lỗi
`ON_ERROR_ROLLBACK`	`interactive` — savepoint ngầm
`VERBOSITY`	`default`, `verbose`, `terse`
`HISTSIZE`	Số lệnh lưu trong lịch sử
`COMP_KEYWORD_CASE`	`upper` — tự hoàn thành từ khóa viết hoa
`PROMPT1`	Định dạng dấu nhắc

6. Lịch sử và chỉnh sửa

Phím / Lệnh	Tác dụng
`↑` / `↓`	Duyệt lịch sử
`Ctrl+R`	Tìm ngược trong lịch sử
`Tab`	Tự động hoàn thành tên bảng/cột/từ khóa
`Ctrl+A` / `Ctrl+E`	Về đầu / cuối dòng
`Ctrl+U`	Xóa từ con trỏ về đầu dòng
`Ctrl+L`	Xóa màn hình
`Ctrl+C`	Hủy câu lệnh đang gõ
`\s`	Xem lịch sử lệnh
`\s file.txt`	Lưu lịch sử ra file
`\r`	Xóa bộ đệm truy vấn
`\p`	Xem bộ đệm truy vấn hiện tại

Tab là tính năng bị đánh giá thấp nhất: gõ SELECT * FROM fi rồi Tab sẽ tự điền film.

7. Trợ giúp

\?              -- danh sách lệnh meta
\?  variables   -- trợ giúp về biến
\h              -- danh sách lệnh SQL
\h SELECT       -- cú pháp đầy đủ của SELECT
\h ALTER TABLE  -- cú pháp ALTER TABLE
\q              -- thoát

\h cực kỳ hữu ích — không cần mở trình duyệt để tra cú pháp.

8. Đo thời gian và phân tích

\timing on
SELECT count(*) FROM payment;
-- Time: 12.345 ms
 
EXPLAIN ANALYZE SELECT * FROM film WHERE title = 'Academy Dinosaur';

→ Công thức thực dụng - EXPLAIN, xóa trùng, so sánh bảng

8.1. `\watch` — chạy lặp lại

SELECT count(*), max(payment_date) FROM payment;
\watch 5      -- chạy lại mỗi 5 giây, Ctrl+C để dừng

Rất hữu ích khi theo dõi tiến trình nạp dữ liệu hoặc quan sát kết nối:

SELECT pid, state, wait_event, left(query, 50)
FROM pg_stat_activity WHERE state <> 'idle';
\watch 2

8.2. `\gexec` — chạy kết quả truy vấn như lệnh SQL

Tính năng mạnh nhất mà ít người biết:

-- Sinh và chạy lệnh ANALYZE cho mọi bảng
SELECT format('ANALYZE %I;', tablename)
FROM pg_tables WHERE schemaname = 'public'
\gexec
 
-- Xóa tất cả bảng có tiền tố tmp_
SELECT format('DROP TABLE %I CASCADE;', tablename)
FROM pg_tables WHERE tablename LIKE 'tmp_%'
\gexec
 
-- Tạo index cho mọi cột khóa ngoại còn thiếu
SELECT format('CREATE INDEX ON %I (%I);', tbl, col) FROM ...
\gexec

9. File cấu hình `~/.psqlrc`

Đây là cấu hình khuyến nghị:

-- Im lặng khi khởi động
\set QUIET 1
 
-- Hiển thị
\x auto
\pset null '[NULL]'
\pset linestyle unicode
\pset border 2
 
-- Tiện ích
\timing on
\set HISTSIZE 10000
\set HISTFILE ~/.psql_history- :DBNAME
\set COMP_KEYWORD_CASE upper
\set VERBOSITY verbose
 
-- An toàn
\set ON_ERROR_ROLLBACK interactive
 
-- Dấu nhắc: user@database:schema
\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]%R%# '
 
-- Truy vấn viết tắt
\set phien 'SELECT pid, state, now()-query_start AS thoi_gian, left(query,60) FROM pg_stat_activity WHERE state <> ''idle'' ORDER BY 3 DESC;'
\set kich_thuoc 'SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS kich_thuoc FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname=''public'' AND c.relkind=''r'' ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;'
\set index_thua 'SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;'
 
\set QUIET 0
\echo 'Đã nạp ~/.psqlrc — gõ :phien, :kich_thuoc, :index_thua'

Dùng:

:phien          -- xem các truy vấn đang chạy
:kich_thuoc     -- 20 bảng lớn nhất
:index_thua     -- index chưa từng được dùng

\set HISTFILE ~/.psql_history- :DBNAME

Mỗi database có file lịch sử riêng — Ctrl+R chỉ tìm trong các lệnh liên quan tới database hiện tại.

10. Truy vấn giám sát hữu ích

-- Kết nối đang hoạt động
SELECT pid, usename, application_name, state,
       now() - query_start AS thoi_gian, left(query, 60) AS truy_van
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY thoi_gian DESC;
 
-- Hủy truy vấn
SELECT pg_cancel_backend(1234);      -- nhẹ nhàng
SELECT pg_terminate_backend(1234);   -- cưỡng bức
 
-- Kích thước database
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;
 
-- Kích thước bảng và index
SELECT relname AS bang,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS tong,
       pg_size_pretty(pg_relation_size(c.oid))       AS du_lieu,
       pg_size_pretty(pg_indexes_size(c.oid))        AS index
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;
 
-- Index chưa từng dùng (ứng viên để xóa)
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS kich_thuoc
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Bảng phình to (cần VACUUM)
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS pct_chet,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
 
-- Khóa đang chờ
SELECT blocked.pid AS bi_chan, blocking.pid AS dang_chan,
       left(blocked.query, 50) AS truy_van_bi_chan
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

11. Bảng tra cứu nhanh

KẾT NỐI              THÔNG TIN            ĐỊNH DẠNG
\c db                \l    database       \x       dọc
\conninfo            \dt   bảng           \x auto  tự động
\q    thoát          \d t  cấu trúc       \a       căn chỉnh
                     \d+ t chi tiết       \t       tiêu đề
FILE                 \di   index          \pset null '[NULL]'
\i  file.sql         \dv   view
\o  file             \df   function       BIẾN
\copy ... TO/FROM    \du   user           \set x 'v'
\e    soạn thảo      \dx   extension      :x  :'x'  :"x"

TIỆN ÍCH             TRỢ GIÚP             LỊCH SỬ
\timing on           \?    lệnh meta      ↑ ↓  duyệt
\watch 5             \h    lệnh SQL       Ctrl+R  tìm
\gexec               \h SELECT            Tab  hoàn thành
\! lenh_shell                             \s   xem lịch sử

12. Bài tập

Tạo ~/.psqlrc với \x auto, \timing on, và \pset null '[NULL]'.
Dùng \d+ xem kích thước tất cả bảng trong dvdrental. Bảng nào lớn nhất?
Dùng \gexec sinh lệnh ANALYZE cho mọi bảng trong schema public.
Xuất kết quả truy vấn ra file HTML.
Dùng \watch theo dõi số dòng của một bảng trong khi chạy INSERT ở phiên khác.
Định nghĩa biến :top_phim trong .psqlrc trả về 10 phim dài nhất.
Tìm các index chưa từng được dùng trong dvdrental.
Chạy một file SQL trong một giao dịch duy nhất, dừng ngay khi có lỗi.

Đáp án

1. Xem mục 9.

\dt+

Hoặc chính xác hơn:

SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS kich_thuoc
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 5;

Bảng lớn nhất thường là rental (16.044 dòng) hoặc payment (14.596 dòng); film cũng lớn vì có cột fulltext với index GiST.

SELECT format('ANALYZE %I;', tablename)
FROM pg_tables WHERE schemaname = 'public'
\gexec

\pset format html
\o bao_cao.html
SELECT title, length, rating FROM film ORDER BY length DESC LIMIT 20;
\o
\pset format aligned

-- Phiên 1
SELECT count(*) FROM film_test;
\watch 2
 
-- Phiên 2
INSERT INTO film_test SELECT * FROM film LIMIT 100;

6. Thêm vào ~/.psqlrc:

\set top_phim 'SELECT title, length FROM film ORDER BY length DESC, film_id LIMIT 10;'

Dùng: gõ :top_phim

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

Lưu ý: thống kê chỉ tích lũy từ lần reset cuối. Index mới tạo hoặc index của khóa chính hiếm dùng có thể xuất hiện ở đây mà vẫn cần thiết.

psql -d dvdrental -1 -v ON_ERROR_STOP=1 -f script.sql

13. Tóm tắt

\d, \dt, \l, \du, \dx là bộ lệnh khám phá cơ bản; thêm + để chi tiết.
\x auto cứu bạn khỏi bảng bị cắt xén — nên bật mặc định.
\pset null '[NULL]' giúp phân biệt NULL với chuỗi rỗng.
\copy cho file trên máy bạn; COPY cho file trên máy chủ.
\gexec sinh và chạy SQL động — công cụ tự động hóa mạnh nhất của psql.
\watch n chạy lặp truy vấn — hữu ích khi giám sát.
\h SELECT tra cú pháp nhanh hơn mở trình duyệt.
psql -1 -v ON_ERROR_STOP=1 -f file.sql là cách chạy migration an toàn.
Đầu tư vào ~/.psqlrc một lần, hưởng lợi mãi mãi.

← Biểu thức điều kiện - CASE, COALESCE, NULLIF, CAST | → Công thức thực dụng - EXPLAIN, xóa trùng, so sánh bảng

description	Làm chủ psql — công cụ dòng lệnh chính thức. Học tốt psql giúp bạn nhanh hơn nhiều so với dùng giao diện đồ họa.
tags	postgresql, psql, cli, tooling, cheatsheet
aliases

TungDD Wiki

Nội dung

Lệnh psql

Lệnh psql

1. Kết nối

1.1. Chạy lệnh không vào shell

2. Lệnh thông tin (`\d`)

2.1. Ví dụ `\d film`

3. Định dạng đầu ra

3.1. `\x` — cứu cánh cho bảng nhiều cột

4. Nhập/xuất và thực thi file

5. Biến và tương tác

5.1. Biến hệ thống hữu ích

6. Lịch sử và chỉnh sửa

7. Trợ giúp

8. Đo thời gian và phân tích

8.1. `\watch` — chạy lặp lại

8.2. `\gexec` — chạy kết quả truy vấn như lệnh SQL

9. File cấu hình `~/.psqlrc`

10. Truy vấn giám sát hữu ích

11. Bảng tra cứu nhanh

12. Bài tập

13. Tóm tắt

Sơ đồ

Mục lục

Liên kết ngược

TungDD Wiki

Nội dung

Lệnh psql

Lệnh psql

1. Kết nối

1.1. Chạy lệnh không vào shell

2. Lệnh thông tin (\d)

2.1. Ví dụ \d film

3. Định dạng đầu ra

3.1. \x — cứu cánh cho bảng nhiều cột

4. Nhập/xuất và thực thi file

5. Biến và tương tác

5.1. Biến hệ thống hữu ích

6. Lịch sử và chỉnh sửa

7. Trợ giúp

8. Đo thời gian và phân tích

8.1. \watch — chạy lặp lại

8.2. \gexec — chạy kết quả truy vấn như lệnh SQL

9. File cấu hình ~/.psqlrc

10. Truy vấn giám sát hữu ích

11. Bảng tra cứu nhanh

12. Bài tập

13. Tóm tắt

Sơ đồ

Mục lục

Liên kết ngược

2. Lệnh thông tin (`\d`)

2.1. Ví dụ `\d film`

3.1. `\x` — cứu cánh cho bảng nhiều cột

8.1. `\watch` — chạy lặp lại

8.2. `\gexec` — chạy kết quả truy vấn như lệnh SQL

9. File cấu hình `~/.psqlrc`