Giới thiệu PostgreSQL

Tình huống mở đầu

Bạn vừa nhận được một cái URL của PostgreSQL và một lời nhắn từ đồng nghiệp: bảng orders của cửa hàng đã có sẵn, 1 hàng dữ liệu, cứ vào mà xem. Bạn mở psql, gõ SELECT * FROM orders; và nhận lại một dòng đỏ: relation "orders" does not exist. Bạn nhắn lại, đồng nghiệp trả lời ngay: bảng vẫn còn đó, mình mới nhìn thấy sáng nay.

Hãy đoán trước: bảng bị xóa, bạn thiếu quyền, hay bạn đang đứng sai chỗ? Cuối bài bạn sẽ chỉ ra chính xác nguyên nhân và sửa nó bằng đúng một lệnh.

Mục tiêu bài học

Sau bài này bạn có thể:

• Vẽ được cây chứa của PostgreSQL: cluster, database, schema, bảng, và chỉ ra public thuộc tầng nào.
• Dùng current_database() và current_schema() để biết phiên đang đứng ở đâu.
• Giải thích vì sao SELECT trong một database không nhìn thấy bảng của database khác, kể cả khi viết đủ tên ba phần.
• Sửa lỗi relation does not exist do sai database, và phân biệt nó với lỗi sai schema.

Bức tranh lớn

Xuyên suốt cả chương, mình ví PostgreSQL như một tòa nhà văn phòng. Cả tòa nhà là cluster: một server đang chạy với một thư mục dữ liệu. Mỗi tầng do một công ty thuê là một database, có khóa riêng. Trong mỗi công ty có các phòng ban là schema, và mỗi phòng ban có những tủ hồ sơ là bảng. Dự án xuyên suốt là cửa hàng nhỏ có database tên demo_shop.

Hình dưới trả lời một câu hỏi: tên một bảng nằm ở đâu trong cây, và hai bảng cùng tên có thể sống chung được không?

Cách đọc hình: đọc từ trên xuống. Ô xanh dương là cả cluster. Hai ô tím ở hàng thứ hai là hai database, ô tím ở hàng thứ ba là hai schema của demo_shop, và hai ô cyan ở cuối là hai bảng cùng tên orders nhưng khác schema. Màu chỉ phân nhóm, chữ trong ô mới là nội dung chính.

Chỗ ẩn dụ không còn đúng: nhân viên thật có thể đi thang máy sang tầng khác và nói chuyện với công ty khác. Trong PostgreSQL, một phiên làm việc đã vào tầng nào thì ở lại tầng đó cho đến khi kết nối lại.

Khái niệm cốt lõi

Bài này có ba khái niệm.

1. Cây chứa: cluster, database, schema, bảng

Một cluster là một server PostgreSQL cùng thư mục dữ liệu của nó. Trong cluster có nhiều database. Trong database có nhiều schema, và schema mới là nơi chứa bảng, view, hàm. Một database mới tạo luôn có sẵn schema public. public là một schema, không phải một database, dù tên của nó nghe giống.

2. Một phiên gắn với một database

Khi psql kết nối, nó mở kết nối vào đúng một database. Muốn làm việc ở database khác, bạn kết nối lại bằng lệnh \c tên_database. Đây là điểm khác với những hệ mà bạn có thể gõ tên_db.tên_bảng để với sang database kề bên.

3. Tên bảng được hiểu theo schema

Tên orders không kèm schema sẽ được tìm lần lượt trong danh sách search_path, mặc định bắt đầu bằng public. Viết sales.orders thì chỉ định thẳng schema. Cùng một tên bảng vì thế có thể tồn tại ở nhiều schema khác nhau.

Ví dụ đầu tiên

Ví dụ nhỏ nhất: tạo database demo_shop, rồi hỏi phiên xem mình đang ở đâu. Đầu tiên là lệnh tạo database, chạy ở bất kỳ database nào đang có:

CREATE DATABASE demo_shop;

Sau đó kết nối vào demo_shop và hỏi ba thứ: database hiện tại, schema hiện tại, role đang dùng.

\c demo_shop
SELECT current_database(), current_schema(), current_user;

Dự đoán trước khi xem kết quả: schema hiện tại tên gì, dù bạn chưa hề tạo schema nào?

Kết quả [Đã chạy: PostgreSQL 16.15 trong container Docker]. Mình bật \pset border 2 trước khi chạy để bảng có khung viền; psql mặc định in cùng dữ liệu nhưng ít đường kẻ hơn:

+------------------+----------------+--------------+
| current_database | current_schema | current_user |
+------------------+----------------+--------------+
| demo_shop        | public         | postgres     |
+------------------+----------------+--------------+
(1 row)

Kết quả cho thấy public có sẵn ngay khi database vừa được tạo. Giờ đến tình huống mở đầu. Trước hết dựng bảng orders trong demo_shop:

\c demo_shop
CREATE TABLE orders (id int, total numeric(10,2));
INSERT INTO orders VALUES (1, 19.99);

Rồi đọc nó khi đã ở đúng database:

\c demo_shop
SELECT * FROM orders;

+----+-------+
| id | total |
+----+-------+
|  1 | 19.99 |
+----+-------+
(1 row)

Và cùng câu SELECT đó khi phiên đang đứng ở database postgres:

\c postgres
SELECT * FROM orders;

Dự đoán trước khi xem: lệnh báo lỗi gì, và lỗi có nói bảng nằm ở database nào không?

Kết quả [Đã chạy]:

ERROR:  relation "orders" does not exist
LINE 1: SELECT * FROM orders;
                      ^

Xem từng bước

Animation dưới đây đi lại đúng tình huống này: sai database, kết nối lại, rồi thử với tên ba phần. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: ba kịch bản trên PostgreSQL 16.15]:

Bạn vừa làm được: từ một thông báo lỗi ngắn, biết ngay phiên đang đứng sai tầng và cách đi đúng.

Concept Check

Bảng orders nằm trong database demo_shop, schema public. Phiên hiện đang kết nối vào database postgres.

Câu 1. Câu SELECT * FROM orders; cho kết quả gì?

• A. Lỗi relation "orders" does not exist, vì phiên chỉ nhìn thấy bảng trong database đang kết nối.
• B. Trả về một hàng, vì PostgreSQL tìm bảng trong mọi database của cluster.
• C. Trả về bảng rỗng, vì database postgres tự tạo bảng trống khi gọi tên.
• D. Lỗi vì thiếu quyền, và quyền sẽ được cấp ngay nếu đăng nhập lại.

Câu 2. Câu SELECT * FROM demo_shop.public.orders; chạy từ database postgres cho kết quả gì?

• A. Trả về một hàng, vì tên ba phần chỉ rõ cả database, schema và bảng.
• B. Lỗi cross-database references are not implemented, vì không thể truy vấn chéo database.
• C. Trả về bảng rỗng, vì dữ liệu chỉ chuyển sang sau khi kết nối lại.
• D. Lỗi cú pháp, vì tên bảng chỉ được viết tối đa hai phần.

Câu 3. public là gì?

• A. Một database có sẵn, chứa các bảng dùng chung cho cả cluster.
• B. Một role đặc biệt mà mọi người dùng đều đăng nhập vào được.
• C. Một schema có sẵn trong mỗi database mới, nằm trong search_path mặc định.
• D. Một thư mục trên đĩa, nơi PostgreSQL lưu dữ liệu của cluster.

Đáp án và giải thích:

• Câu 1: A. B sai vì PostgreSQL không tìm xuyên database. C sai vì không có bảng nào được tự tạo. D sai vì thông báo là does not exist, không phải permission denied.
• Câu 2: B. A sai vì tên ba phần chỉ được chấp nhận khi phần database trùng với database đang kết nối. C sai vì không có chuyển dữ liệu nào. D sai vì cú pháp ba phần hợp lệ, lỗi nằm ở chỗ khác database.
• Câu 3: C. A nhầm schema với database. B nhầm với nhóm quyền mặc định tên PUBLIC. D nhầm với thư mục dữ liệu của cluster.

Làm cùng mình

Công ty thuê thêm một phòng ban nhân sự. Mình dựng schema hr trong demo_shop, bảng hr.staff có một nhân viên, rồi đếm số nhân viên. Mục tiêu: tự thấy rằng tên bảng không kèm schema chỉ tìm trong search_path.

Bước 1. Vẽ ra giấy

Vẽ cây gồm cluster, database demo_shop, hai schema public và hr, bảng hr.staff. Dự đoán: câu SELECT count(*) FROM staff; có chạy được không?

Bước 2. Điền chỗ trống

\c demo_shop
-- TODO 1: tạo schema hr
-- TODO 2: tạo bảng hr.staff gồm id kiểu int và full_name kiểu text
INSERT INTO hr.staff VALUES (1, 'An');
-- TODO 3: đếm số hàng của staff, viết thẳng tên bảng không kèm schema

Gợi ý: CREATE SCHEMA hr;, rồi CREATE TABLE hr.staff (...). Dòng đếm cuối cùng viết SELECT count(*) FROM staff;.

Chạy xong bạn sẽ thấy [Đã chạy] một lỗi. So sánh với dự đoán ở Bước 1. Đáp án ở mục Lời giải và đáp án.

Bước 3. Sửa lỗi

Đổi dòng đếm sao cho chạy được mà không đổi search_path. Dự đoán trước: kết quả đếm là bao nhiêu?

Bước 4. Vẽ lại ra giấy

Ghi một câu: vì sao staff không tìm thấy hr.staff, dù cả hai cùng nằm trong demo_shop.

Thử phá nó đi

Ba lỗi người mới hay gặp [Đã chạy: PostgreSQL 16.15]:

Kết quả thật của lỗi thứ ba:

SHOW DATABASES;

ERROR:  unrecognized configuration parameter "databases"

Hiểu lầm quan trọng nhất của bài này là coi public là một database và coi một lỗi sai tầng là mất dữ liệu. Hình dưới đối chiếu hai tình huống.

Cách đọc hình: đọc từ trên xuống dưới như một cuộc gọi hỗ trợ. Ô đỏ là lúc bạn nghĩ bảng đã biến mất. Hai ô tím và ô xanh lá phía sau cho thấy chỉ cần kết nối lại đúng database thì cùng câu lệnh chạy bình thường.

Về schema: nếu cùng tên bảng có ở nhiều schema, PostgreSQL chọn schema theo search_path. Hai bảng orders trong cùng một database có thể tồn tại cùng lúc, như kết quả thật dưới đây:

\c demo_shop
CREATE SCHEMA sales;
CREATE TABLE public.orders (id int, total numeric(10,2));
CREATE TABLE sales.orders (id int, note text);
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'orders'
ORDER BY table_schema;

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| public       | orders     |
| sales        | orders     |
+--------------+------------+
(2 rows)

Khi chỉ có sales.orders và bạn viết tên trần orders với search_path mặc định, PostgreSQL không tìm trong sales:

\c demo_shop
CREATE SCHEMA sales;
CREATE TABLE sales.orders (id int);
SELECT count(*) FROM orders;

ERROR:  relation "orders" does not exist
LINE 1: SELECT count(*) FROM orders;
                             ^

Đưa sales lên đầu search_path bằng SET search_path TO sales; thì tên trần orders được hiểu là sales.orders:

\c demo_shop
CREATE SCHEMA sales;
CREATE TABLE sales.orders (id int);
SET search_path TO sales;
SELECT count(*) FROM orders;

+-------+
| count |
+-------+
|     0 |
+-------+
(1 row)

Tự tay làm

Dùng chung database demo_shop của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Điền bảng trace. Đầu vào: demo_shop có bảng public.orders. Điền bảng cho ba câu lệnh, mỗi câu chạy khi phiên đang đứng ở database ghi trong cột đầu:

Tiêu chí đạt: đúng cả ba ô. Gợi ý: schema public tồn tại ở mọi database, nhưng bảng thì không.

Vận dụng

Bài 2. Đếm hai phòng ban. Đầu vào: trong demo_shop có public.orders với hai hàng (id 1, 2) và sales.orders với ba hàng (id 10, 20, 30). Viết một câu SQL duy nhất trả ra hai dòng: tên schema và số hàng của từng bảng. Tiêu chí đạt: kết quả có hai dòng, public hai hàng và sales ba hàng. Gợi ý: nối hai câu SELECT count(*) bằng UNION ALL, và viết tên bảng kèm schema.

Thử thách (tùy chọn)

Bài 3. Tìm bảng khi quên database. Đầu vào: bạn không nhớ bảng staff nằm ở database nào trong cluster. Viết các bước, bằng lời, để tìm ra. Tiêu chí đạt: nêu được rằng phải kết nối vào từng database để kiểm tra và nêu một truy vấn trong information_schema.tables dùng để kiểm tra từng database. Gợi ý: lệnh liệt kê database nằm ở bài sau của chương.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) Kể tên bốn tầng của cây chứa, từ lớn đến nhỏ.
2. (Mục tiêu 2, dự đoán) Vừa tạo database mới và kết nối vào, current_schema() trả về gì?
3. (Mục tiêu 3, giải thích) Vì sao viết demo_shop.public.orders từ database postgres vẫn lỗi?
4. (Mục tiêu 4, tìm lỗi) Đồng nghiệp báo relation "orders" does not exist dù bảng chắc chắn có trong cluster. Hai nguyên nhân đầu tiên bạn kiểm tra là gì?
5. (Mục tiêu 1, so sánh) Khác biệt giữa database và schema là gì, nhìn từ phía người viết câu SELECT?

Điều cần nhớ

• Cây chứa của PostgreSQL là cluster, database, schema, bảng.
• Một phiên gắn với đúng một database; muốn sang database khác phải kết nối lại.
• public là schema có sẵn, không phải database.
• Gặp relation does not exist, kiểm tra database và schema hiện tại trước khi nghi ngờ dữ liệu.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: nếu bạn đang ở demo_shop mà muốn tạo một database mới tên demo_shop_test giống hệt demo_shop, bạn sẽ làm thế nào?

Đào sâu

Phần này dành cho bạn tò mò.

• Phiên bản. Máy mình chạy bản này:

SHOW server_version;

+----------------+
| server_version |
+----------------+
| 16.15          |
+----------------+
(1 row)

[Đã chạy: PostgreSQL 16.15 trong container Docker]. Máy của bạn có thể ra số khác.

• Extension. PostgreSQL mở rộng bằng extension. Danh sách extension cài sẵn được trên máy này, kiểm tra thật bằng view pg_available_extensions:

SELECT name, default_version
FROM pg_available_extensions
WHERE name IN ('pgcrypto', 'uuid-ossp', 'postgis', 'pg_trgm')
ORDER BY name;

+-----------+-----------------+
|   name    | default_version |
+-----------+-----------------+
| pg_trgm   | 1.6             |
| pgcrypto  | 1.3             |
| uuid-ossp | 1.1             |
+-----------+-----------------+
(3 rows)

Có ba extension, và không có postgis trên bản Docker này. Muốn dùng PostGIS bạn phải cài thêm gói tương ứng. Việc liệt kê chỉ cho biết extension có sẵn để cài, chưa phải đã bật trong database.

• Kiểu dữ liệu phong phú. Kiểu jsonb cho phép truy vấn thẳng vào dữ liệu JSON:

SELECT '{"user_id": 10, "paid": true}'::jsonb ->> 'user_id' AS user_id;

+---------+
| user_id |
+---------+
| 10      |
+---------+
(1 row)

Toán tử ->> lấy một khóa ra dạng chữ. Chương kiểu dữ liệu sẽ nói kỹ về jsonb.

• Hiểu lầm nâng cao. Với MySQL, database và schema gần như là một. Trong PostgreSQL chúng là hai tầng riêng, nên khi chuyển từ MySQL sang, câu USE shop tương ứng với \c shop, còn schema thì phải chọn thêm.
• Giới hạn của bài. Bài này chưa nói đến role và phân quyền, search_path chi tiết, và cách truy vấn chéo database bằng postgres_fdw.
• Đọc thêm. Tài liệu PostgreSQL 16, chương Schemas: https://www.postgresql.org/docs/16/ddl-schemas.html

Bài tiếp theo

Bài sau là cài PostgreSQL trên Windows. Mình sẽ đi qua installer, kiểm tra service, và dùng chính các lệnh trong bài này để xác nhận cài xong. Mini-dự án nối tiếp: dựng cluster trên máy bạn và tạo lại database demo_shop.

Lời giải và đáp án

Làm cùng mình, Bước 2. Ba chỗ cần điền:

CREATE SCHEMA hr;
CREATE TABLE hr.staff (id int, full_name text);
SELECT count(*) FROM staff;

ERROR:  relation "staff" does not exist
LINE 1: SELECT count(*) FROM staff;
                             ^

Lỗi xảy ra vì staff không kèm schema nên PostgreSQL chỉ tìm trong search_path, mặc định là public, mà staff nằm trong hr.

Làm cùng mình, Bước 3. Viết kèm schema:

\c demo_shop
CREATE SCHEMA hr;
CREATE TABLE hr.staff (id int, full_name text);
INSERT INTO hr.staff VALUES (1, 'An');
SELECT count(*) FROM hr.staff;

+-------+
| count |
+-------+
|     1 |
+-------+
(1 row)

Kết quả đếm là 1.

Bài 1. Hàng thứ nhất: một hàng (id 1, total 19.99). Hàng thứ hai: lỗi relation "public.orders" does not exist, vì public có ở mọi database nhưng bảng thì không. Hàng thứ ba: lỗi cross-database references are not implemented.

Bài 2.

\c demo_shop
CREATE SCHEMA sales;
CREATE TABLE public.orders (id int);
CREATE TABLE sales.orders (id int);
INSERT INTO public.orders VALUES (1), (2);
INSERT INTO sales.orders VALUES (10), (20), (30);
SELECT 'public' AS schema_name, count(*) AS n FROM public.orders
UNION ALL
SELECT 'sales', count(*) FROM sales.orders;

+-------------+---+
| schema_name | n |
+-------------+---+
| public      | 2 |
| sales       | 3 |
+-------------+---+
(2 rows)

Bài 3. Dùng \l để lấy danh sách database. Với từng database, kết nối bằng \c tên, rồi chạy SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'staff';. Database nào trả về dòng thì bảng nằm ở đó. Cách này bắt buộc phải kết nối từng database vì information_schema chỉ liệt kê bảng của database đang kết nối.

Tự kiểm tra.

1. Cluster, database, schema, bảng.
2. public.
3. PostgreSQL không cài đặt truy vấn chéo database. Tên ba phần chỉ được chấp nhận khi phần database trùng với database đang kết nối.
4. Kiểm tra SELECT current_database(); xem có đang ở đúng database không, rồi kiểm tra bảng nằm ở schema nào và search_path có chứa schema đó không.
5. Database là ranh giới kết nối: phiên chỉ thấy một database. Schema là nơi gom tên bảng bên trong database, và một câu SELECT có thể đọc nhiều schema của cùng database.