Mọi kiểu trong PostgreSQL đều có thể là một mảng. Khai báo cột là text[] hoặc int[], viết giá trị dưới dạng '{a,b}' hoặc ARRAY['a', 'b'], và đọc phần tử bằng tags[1] (chỉ số bắt đầu từ 1). Để kiểm tra mảng có chứa một giá trị không, dùng 'value' = ANY(tags) hoặc tags @> ARRAY['value'].
Bài viết 1 và 2 có tag sql. tags[1] là phần tử đầu tiên và cardinality đếm số phần tử. Hai cách viết literal lưu cùng một giá trị; ARRAY[...] tiện hơn khi phần tử đến từ biểu thức hoặc chứa dấu phẩy.
Kiểm tra mảng có chứa một giá trị
Bốn toán tử dùng để kiểm tra phần tử:
| Phép kiểm tra | Ý nghĩa | Đúng với '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | một giá trị có trong mảng | có |
tags @> '{sql,json}' | mảng chứa tất cả các giá trị này | có |
tags <@ '{sql,json,go}' | mọi phần tử đều có trong danh sách này | có |
tags && '{go,json}' | hai mảng có ít nhất một phần tử chung | có |
Chỉ bài viết 1 có cả hai tag. Bài viết 2 và 3 có phần tử chung với {go,json}. Bài viết 3 là bài duy nhất có no_go là false. ALL là cặp đối của ANY: phép so sánh phải đúng với mọi phần tử.
= ANY tuân theo quy tắc NULL của SQL. Nếu không tìm thấy giá trị và mảng có chứa NULL, kết quả là NULL, không phải false. Vì vậy WHERE NOT ('x' = ANY(tags)) có thể bỏ sót những hàng bạn mong đợi. @> và && chỉ trả về true hoặc false.
Một index GIN giúp @>, <@, && và = chạy nhanh trên bảng lớn. Nó không giúp được = ANY(column), nên khi cần tra cứu qua index, hãy viết tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Biến mảng thành hàng với unnest
unnest trả về một hàng cho mỗi phần tử, nên bạn có thể đếm, join hoặc lọc trên từng phần tử:
sql xuất hiện trong hai bài viết, các tag khác mỗi tag một bài. Thêm WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) để lấy cả vị trí của từng phần tử. Để gom các hàng lại thành mảng, dùng array_agg (xem string_agg và array_agg).
Thêm, xóa và thay đổi phần tử
Kết quả là {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} và a, b, c. Trong một lệnh UPDATE, các hàm này thay đổi mảng đã lưu: SET tags = array_append(tags, 'new') hoặc SET tags = array_remove(tags, 'old'). array_remove xóa mọi bản sao của giá trị. string_to_array('a,b,c', ',') tách văn bản thành mảng.
Lấy độ dài của mảng
cardinality(tags) trả về số phần tử, và 0 với mảng rỗng. array_length(tags, 1) trả về độ dài của chiều thứ 1, và NULL với mảng rỗng. Vì vậy WHERE array_length(tags, 1) = 0 không bao giờ khớp. Hãy kiểm tra mảng rỗng bằng cardinality(tags) = 0 hoặc tags = '{}'.
Mảng trong PostgreSQL có thể có nhiều chiều (int[][]), nhưng mọi mảng con phải có cùng độ dài, và kích thước khai báo (int[3]) không được áp dụng. Với dữ liệu lồng nhau hoặc không đều, JSONB thường phù hợp hơn.
Câu hỏi thường gặp
Mảng trong PostgreSQL bắt đầu từ 0 hay từ 1?
Mặc định từ 1: tags[1] là phần tử đầu tiên. Một chỉ số vượt quá cuối mảng trả về NULL thay vì báo lỗi.
IN và = ANY trong PostgreSQL khác nhau thế nào?
x IN (1, 2, 3) nhận một danh sách viết sẵn trong truy vấn; x = ANY(array) nhận một giá trị mảng duy nhất. ANY hợp với code ứng dụng, vì bạn có thể truyền một tham số mảng (WHERE id = ANY($1)) thay vì phải dựng một dãy placeholder.
Nên dùng cột mảng hay một bảng riêng?
Mảng hợp với một danh sách ngắn mà bạn đọc và ghi cùng hàng của nó, ví dụ tag. Khi các phần tử cần cột riêng, khóa ngoại, hoặc được cập nhật từng cái một, một bảng riêng với mỗi phần tử một hàng sẽ dễ truy vấn và ràng buộc hơn.
Làm sao kiểm tra một mảng có rỗng không trong PostgreSQL?
Dùng cardinality(arr) = 0 hoặc arr = '{}'. array_length(arr, 1) = 0 không bao giờ khớp, vì array_length trả về NULL với mảng rỗng.