Kiểu dữ liệu, biểu thức và hàm thông dụng
Chọn đúng kiểu dữ liệu (đặc biệt tiền và thời gian), thành thạo hàm chuỗi/ngày giờ, CASE, COALESCE, ép kiểu — những thứ dùng trong gần như mọi truy vấn thực tế.
Chọn sai kiểu dữ liệu là loại lỗi đắt nhất để sửa vì dữ liệu production đã nằm sẵn trong đó:
tiền lưu bằng float cộng ra 0.30000000000000004, giờ lưu không kèm múi giờ lệch 7 tiếng khi chuyển server, email so sánh phân biệt hoa/thường làm tạo trùng tài khoản.
Còn hàm ngày giờ và CASE là công cụ hàng ngày của mọi báo cáo.
🎯 Mục tiêu học tập
- Chọn đúng kiểu cho số, tiền, chuỗi, boolean, ngày giờ; biết khi nào dùng numeric thay vì float
- Làm việc đúng với timestamptz, múi giờ, date_trunc, extract, interval, generate_series
- Thành thạo CASE, COALESCE, NULLIF, CAST (::) và các hàm chuỗi thông dụng
- Tránh các bẫy: chia nguyên, làm tròn, so sánh chuỗi, múi giờ khi nhóm theo ngày
Số: integer, bigint, numeric, float — và quy tắc cho tiền
| Kiểu | Phạm vi / đặc điểm | Dùng cho |
|---|---|---|
smallint / integer / bigint | 2 / 4 / 8 byte; ±32K / ±2,1 tỷ / ±9,2×10¹⁸ | Đếm, id (bigint cho bảng lớn) |
numeric(p,s) | Chính xác tuyệt đối, chậm hơn | Tiền, tỉ lệ cần chính xác |
real / double precision | Dấu phẩy động nhị phân, xấp xỉ | Đo đạc khoa học, thống kê |
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8 AS float_bang_nhau,
0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_bang_nhau,
0.1::float8 + 0.2::float8 AS tong_float; float_bang_nhau | numeric_bang_nhau | tong_float
-----------------+-------------------+---------------------
f | t | 0.30000000000000004
(1 row)Số thực nhị phân không biểu diễn chính xác 0.1, nên cộng dồn hàng triệu giao dịch sinh lệch từng đồng và đối soát không bao giờ khớp.
Dùng numeric(12,2) (hoặc lưu số nguyên theo đơn vị nhỏ nhất như đồng/xu — bigint). Với VND không có phần lẻ, bigint hoặc numeric(14,0) đều ổn. Tránh kiểu money của PostgreSQL (phụ thuộc locale).
SELECT round(2.5) AS lam_tron_numeric,
round(2.5::float8) AS lam_tron_float,
round(1234.5678, 2) AS hai_so_le,
round(1234.5678, -2) AS hang_tram,
ceil(7 / 2.0) AS tran, floor(7 / 2.0) AS san, trunc(-3.7) AS cat; lam_tron_numeric | lam_tron_float | hai_so_le | hang_tram | tran | san | cat
------------------+----------------+-----------+-----------+------+-----+-----
3 | 2 | 1234.57 | 1200 | 4 | 3 | -3
(1 row)round(numeric) làm tròn ra xa 0 (2,5 → 3) còn round(float8) theo quy tắc làm tròn về số chẵn gần nhất của chuẩn IEEE (2,5 → 2). Một lý do nữa để dùng numeric cho tiền.
Làm tròn ở bước cuối cùng (hiển thị), không làm tròn từng dòng rồi mới cộng — sẽ tích luỹ sai số.
Chuỗi: text, varchar và các hàm hay dùng
Trong PostgreSQL text và varchar(n) hiệu năng như nhau; char(n) (độn khoảng trắng) nên tránh.
Mặc định dùng text, thêm CHECK (length(x) <= n) nếu cần giới hạn.
SELECT full_name,
upper(full_name) AS viet_hoa,
length(full_name) AS so_ky_tu,
split_part(email, '@', 1) AS ten_dang_nhap,
left(full_name, strpos(full_name, ' ') - 1) AS ho,
replace(email, '@example.com', '') AS bo_domain,
lpad(id::text, 6, '0') AS ma_khach
FROM customers
ORDER BY id
LIMIT 4; full_name | viet_hoa | so_ky_tu | ten_dang_nhap | ho | bo_domain | ma_khach
-----------------+-----------------+----------+---------------+-------+-----------+----------
Trần Quốc Dũng | TRẦN QUỐC DŨNG | 14 | user1 | Trần | user1 | 000001
Lê Ngọc Lan | LÊ NGỌC LAN | 11 | user2 | Lê | user2 | 000002
Phạm Đức Phúc | PHẠM ĐỨC PHÚC | 13 | user3 | Phạm | user3 | 000003
Hoàng Minh Châu | HOÀNG MINH CHÂU | 15 | user4 | Hoàng | user4 | 000004
(4 rows)SELECT trim(' xin chào ') AS cat_khoang_trang,
concat_ws(' - ', 'A', NULL, 'C') AS noi_bo_qua_null,
'A' || NULL || 'C' AS toan_tu_noi_gap_null,
substring('SKU-12345' FROM 5) AS tu_vi_tri_5,
regexp_replace('0901-234-567', '\D', '', 'g') AS chi_giu_so,
string_to_array('a,b,c', ',') AS thanh_mang; cat_khoang_trang | noi_bo_qua_null | toan_tu_noi_gap_null | tu_vi_tri_5 | chi_giu_so | thanh_mang
------------------+-----------------+----------------------+-------------+------------+------------
xin chào | A - C | NULL | 12345 | 0901234567 | {a,b,c}
(1 row)'A@x.com' = 'a@x.com' là FALSE nên có thể tạo hai tài khoản trùng email. Chuẩn hoá khi lưu (lower(trim(email))) và/hoặc tạo unique index trên biểu thức:
CREATE UNIQUE INDEX ON users (lower(email)) (module 9, 11). Với tìm kiếm không phân biệt hoa/thường: ILIKE hoặc extension citext.
Mẫu bắt đầu bằng % không dùng được index B-tree thông thường → quét toàn bảng. Giải pháp: pg_trgm + GIN index, hoặc full-text search (module 13).
Ngày giờ: date, timestamp, timestamptz, interval
| Kiểu | Lưu gì | Khuyến nghị |
|---|---|---|
date | Chỉ ngày | Ngày sinh, ngày hiệu lực |
timestamp (without tz) | Ngày giờ "trần", không múi giờ | Hiếm khi nên dùng cho sự kiện thật |
timestamptz | Một thời điểm tuyệt đối (lưu UTC, hiển thị theo múi giờ phiên) | Mặc định cho mọi thời điểm sự kiện |
interval | Khoảng thời gian | Cộng/trừ thời gian |
SELECT timestamptz '2024-03-01 00:30+07' AS gio_viet_nam,
timestamptz '2024-03-01 00:30+07' AT TIME ZONE 'UTC' AS gio_utc,
(timestamptz '2024-03-01 00:30+07')::date AS ngay_theo_mui_gio_phien,
date_trunc('month', timestamptz '2024-03-17 15:45+07') AS dau_thang,
date_trunc('week', timestamptz '2024-03-17 15:45+07') AS dau_tuan_thu_hai,
extract(dow FROM date '2024-03-17') AS thu_0_la_chu_nhat,
extract(year FROM date '2024-03-17') AS nam; gio_viet_nam | gio_utc | ngay_theo_mui_gio_phien | dau_thang | dau_tuan_thu_hai | thu_0_la_chu_nhat | nam
------------------------+---------------------+-------------------------+------------------------+------------------------+-------------------+------
2024-03-01 00:30:00+07 | 2024-02-29 17:30:00 | 2024-03-01 | 2024-03-01 00:00:00+07 | 2024-03-11 00:00:00+07 | 0 | 2024
(1 row)SELECT date '2024-01-31' + interval '1 month' AS cong_1_thang,
date '2024-03-10' - date '2024-03-01' AS so_ngay_chenh,
age(date '2024-03-01', date '2019-03-01') AS tuoi,
now() - interval '7 days' < now() AS bieu_thuc_hop_le,
to_char(timestamptz '2024-03-17 15:45+07', 'DD/MM/YYYY HH24:MI') AS dinh_dang; cong_1_thang | so_ngay_chenh | tuoi | bieu_thuc_hop_le | dinh_dang
---------------------+---------------+---------+------------------+------------------
2024-02-29 00:00:00 | 9 | 5 years | t | 17/03/2024 15:45
(1 row)-- generate_series: tạo dãy ngày để lấp 'ngày không có đơn' trong báo cáo
SELECT d::date AS ngay
FROM generate_series(date '2024-03-01', date '2024-03-05', interval '1 day') AS d; ngay
------------
2024-03-01
2024-03-02
2024-03-03
2024-03-04
2024-03-05
(5 rows)Đơn lúc 00:30 giờ Việt Nam là 17:30 UTC ngày hôm trước. Nếu server/phiên đặt UTC mà bạn GROUP BY ordered_at::date thì doanh thu "ngày 1" bị chuyển sang ngày 29/28 — báo cáo lệch so với kế toán.
Luôn chuyển múi giờ rõ ràng: (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date, hoặc date_trunc('day', ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh'). Quy tắc: lưu UTC (timestamptz), hiển thị và nhóm theo múi giờ nghiệp vụ.
-- Đơn đặt sau nửa đêm giờ VN: ngày theo UTC và ngày theo VN khác nhau
SELECT id, ordered_at,
(ordered_at AT TIME ZONE 'UTC')::date AS ngay_utc,
(ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS ngay_vn
FROM orders
WHERE (ordered_at AT TIME ZONE 'UTC')::date <> (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date
ORDER BY id
LIMIT 4; id | ordered_at | ngay_utc | ngay_vn
----+------------------------+------------+------------
1 | 2024-02-01 03:00:00+07 | 2024-01-31 | 2024-02-01
9 | 2024-04-14 03:00:00+07 | 2024-04-13 | 2024-04-14
10 | 2024-05-16 03:00:00+07 | 2024-05-15 | 2024-05-16
26 | 2024-03-11 03:00:00+07 | 2024-03-10 | 2024-03-11
(4 rows)SELECT count(*) FILTER (WHERE (ordered_at AT TIME ZONE 'UTC')::date <> (ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date) AS don_lech_ngay,
count(*) AS tong_don
FROM orders; don_lech_ngay | tong_don
---------------+----------
93 | 400
(1 row)'2024-01-31' + interval '1 month' cho 2024-02-29 (kẹp về ngày cuối tháng), không phải 31/2 — và + 1 month + 1 month không bằng + 2 months trong một số trường hợp. Dùng khi tính hạn hợp đồng/đăng ký định kỳ phải kiểm tra quy tắc nghiệp vụ.
CASE, COALESCE, NULLIF, ép kiểu
CASE là "if/else" của SQL — là một biểu thức trả về giá trị, dùng được ở SELECT, WHERE, ORDER BY và bên trong hàm tổng hợp (module 4).
SELECT name, price,
CASE
WHEN price < 500000 THEN 'Rẻ'
WHEN price < 5000000 THEN 'Trung bình'
WHEN price < 20000000 THEN 'Cao cấp'
ELSE 'Siêu cao cấp'
END AS phan_khuc
FROM products
ORDER BY price
LIMIT 8; name | price | phan_khuc
--------------------------+-----------+------------
Cáp USB-C 1m | 120000.00 | Rẻ
Áo thun basic | 199000.00 | Rẻ
Chuột không dây | 350000.00 | Rẻ
Áo sơ mi công sở | 399000.00 | Rẻ
Quạt đứng cũ (ngừng bán) | 450000.00 | Rẻ
Sạc nhanh 65W | 490000.00 | Rẻ
Bộ dao nhà bếp 6 món | 590000.00 | Trung bình
Áo khoác gió | 690000.00 | Trung bình
(8 rows)SELECT status,
CASE status WHEN 'delivered' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END AS thu_tu_uu_tien
FROM orders
GROUP BY status
ORDER BY thu_tu_uu_tien, status; status | thu_tu_uu_tien
-----------+----------------
delivered | 1
shipped | 2
cancelled | 3
paid | 3
pending | 3
(5 rows)SELECT COALESCE(city, 'Chưa rõ') AS thanh_pho,
count(*)
FROM customers
GROUP BY COALESCE(city, 'Chưa rõ')
ORDER BY 2 DESC; thanh_pho | count
-----------+-------
Hà Nội | 12
Hải Phòng | 11
Cần Thơ | 11
Đà Nẵng | 11
TP.HCM | 11
Chưa rõ | 4
(6 rows)SELECT 10 / NULLIF(0, 0) AS chia_cho_nullif,
'123'::int + 1 AS ep_kieu,
CAST('2024-03-01' AS date) AS ep_ngay,
'12abc'::int AS ep_that_bai;ERROR: invalid input syntax for type integer: "12abc"
LINE 4: '12abc'::int AS ep_that_bai;
^Sắp xếp theo thứ tự nghiệp vụ không theo bảng chữ cái: ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END.
Lưu ý CASE trả về nhiều nhánh phải cùng kiểu; nhánh ELSE bị quên sẽ trả NULL (dễ quên khi chuyển thành cột báo cáo).
x::int ném lỗi nếu một dòng không hợp lệ ('12abc') và cả truy vấn thất bại. Khi nhập dữ liệu bẩn: làm sạch trước bằng regex
(CASE WHEN x ~ '^\d+$' THEN x::int END) hoặc từ PostgreSQL 16 dùng pg_input_is_valid(x, 'int').
🏋️ Bài tập thực hành
Với mỗi khách hàng (5 khách đầu theo id), hiển thị tên, ngày đăng ký (theo giờ Việt Nam) và nhãn: 'Cũ' nếu đăng ký trước 2024-03-01, ngược lại 'Mới'.
Xem lời giải & giải thích
SELECT id, full_name,
(created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS ngay_dk,
CASE WHEN created_at < '2024-03-01' THEN 'Cũ' ELSE 'Mới' END AS nhom
FROM customers ORDER BY id LIMIT 5; id | full_name | ngay_dk | nhom
----+-----------------+------------+------
1 | Trần Quốc Dũng | 2024-01-04 | Cũ
2 | Lê Ngọc Lan | 2024-01-07 | Cũ
3 | Phạm Đức Phúc | 2024-01-10 | Cũ
4 | Hoàng Minh Châu | 2024-01-13 | Cũ
5 | Vũ Gia Huy | 2024-01-16 | Cũ
(5 rows)Từ customers.full_name (dạng 'Họ Đệm Tên'), lấy tên cuối cùng của 5 khách đầu (gợi ý: regexp_replace hoặc split_part với reverse).
Xem lời giải & giải thích
SELECT full_name,
(regexp_match(full_name, '(\S+)$'))[1] AS ten
FROM customers ORDER BY id LIMIT 5; full_name | ten
-----------------+------
Trần Quốc Dũng | Dũng
Lê Ngọc Lan | Lan
Phạm Đức Phúc | Phúc
Hoàng Minh Châu | Châu
Vũ Gia Huy | Huy
(5 rows)regexp_match trả về mảng các nhóm bắt được; [1] lấy nhóm đầu. Cách khác: reverse(split_part(reverse(full_name), ' ', 1)).
Đếm số đơn mỗi ngày từ 2024-03-01 đến 2024-03-07 (theo giờ VN), kể cả ngày không có đơn phải hiện 0.
Xem lời giải & giải thích
SELECT d::date AS ngay, count(o.id) AS so_don
FROM generate_series(date '2024-03-01', date '2024-03-07', interval '1 day') AS d
LEFT JOIN orders o
ON (o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date = d::date
GROUP BY d
ORDER BY d; ngay | so_don
------------+--------
2024-03-01 | 1
2024-03-02 | 1
2024-03-03 | 1
2024-03-04 | 0
2024-03-05 | 2
2024-03-06 | 2
2024-03-07 | 0
(7 rows)Dãy ngày làm bảng bên trái, LEFT JOIN sang dữ liệu; count(o.id) (đếm cột bảng phải) cho 0 với ngày trống — nếu dùng count(*) sẽ ra 1. Chi tiết ở module 4 & 5.
📚 Tài nguyên học tập
-
PostgreSQL — Data TypesDanh mục toàn bộ kiểu dữ liệuDocs
-
PostgreSQL — Date/Time Functionsdate_trunc, extract, AT TIME ZONE, intervalDocs
-
Don't Do This — PostgreSQL WikiDanh sách những điều không nên làm với kiểu dữ liệu và cú phápBài viết
✅ Tự đánh giá hoàn thành
- Biết dùng numeric (không float) cho tiền và timestamptz cho thời điểm sự kiện
- Nhóm theo ngày luôn kèm chuyển múi giờ nghiệp vụ rõ ràng
- Dùng thành thạo CASE, COALESCE, NULLIF, ::cast và hàm chuỗi/ngày giờ chính
- Biết cách tạo dãy ngày bằng generate_series để báo cáo không bị 'lỗ hổng' ngày trống