Cách lấy danh sách bảng và cấu trúc bảng đã tạo trong PostgreSQL...

Bài viết này sẽ mô tả cách lấy danh sách bảng và cấu trúc bảng đã tạo trong PostgreSQL.

Nội dung chính:

Sử dụng lệnh ¥dt
Lấy từ danh mục hệ thống pg_tables
Lấy toàn bộ định nghĩa, cấu trúc của bảng
Lấy danh sách các cột có trong bảng

1. Sử dụng lệnh ¥dt

Đầu tiên là sử dụng lệnh ¥dt để lấy danh sách các bảng đã tạo sau khi kết nối với cơ sở dữ liệu đích muốn thao tác:

¥dt [schema_name.*]

Chẳng hạn muốn lấy danh sách các bảng có trong database sampledata:

Lấy danh sách các bảng đã tạo trong PostgreSQL (1)

Khi lệnh ¥dt được thực thi mà không chỉ định schema_name thì mặc định sẽ lấy danh sách các bảng được tạo trong lược đồ (schema) public để hiển thị.

Để nhận danh sách các bảng được tạo trong một lược đồ (schema) cụ thể ngoài public thì chúng ta cần chỉ định rõ ràng schema_name.

Chẳng hạn muốn lấy danh sách các bảng có trong lược đồ myschema của database sampledata:

¥dt myschema.*

Lấy danh sách các bảng đã tạo trong PostgreSQL (2)

2. Lấy từ danh mục hệ thống pg_tables

Tiếp theo là cách lấy từ pg_tables, một trong những danh mục hệ thống (system catalogs) của PostgreSQL. Danh mục hệ thống là một bảng được sử dụng bởi hệ thống quản lý PostgreSQL và được sử dụng để quản lý thông tin như cơ sở dữ liệu và bảng,… pg_tables có các cột sau:

Tên cột	Kiểu dữ liệu	Nơi tham chiếu	Mô tả
schemaname	name	pg_namespace.nspname	Tên schema
tablename	name	pg_class.relname	Tên table
tableowner	name	pg_authid.rolname	Owner của table
tablespace	name	pg_tablespace.spcname	Tên của tablespace mà chứa table (NULL nếu là database default)
hasindexes	boolean	pg_class.relhasindex	True, nếu table có (hoặc gần đây có) một index
hasrules	boolean	pg_class.relhasrules	True, nếu table có (hoặc gần đây có) một rule
hastriggers	boolean	pg_class.relhastriggers	True, nếu table có (hoặc gần đây có) một trigger
rowsecurity	boolean	pg_class.relrowsecurity	True, nếu security của table row là enabled

Bây giờ, kết nối với cơ sở dữ liệu đích sampledata sẽ lấy dữ liệu của các cột schemaname, tablename, tableowner từ pg_tables.

SELECT schemaname, tablename, tableowner 
  FROM pg_tables;

Sau khi thực thi các tên bảng được sử dụng bởi hệ thống PostgreSQL cũng sẽ được hiển thị:

Lấy danh sách bảng PostgreSQL từ danh mục hệ thống pg_tables (1)

vì vậy để chỉ hiển thị các bảng do người dùng tạo, chúng ta sẽ thêm mệnh đề WHERE và thực thi như sau:

SELECT schemaname, tablename, tableowner 
  FROM pg_tables 
  WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'information_schema';

Lấy danh sách bảng PostgreSQL từ danh mục hệ thống pg_tables (2)

3. Lấy toàn bộ định nghĩa, cấu trúc của bảng

Chúng ta có thể lấy toàn bộ định nghĩa của bảng bằng cách sử dụng lệnh pg_dump được lưu trữ trong thư mục bin của cùng thư mục cài đặt PostgreSQL như lệnh psql.

Ngoài ra, lệnh pg_dump còn được sử dụng để sao lưu cơ sở dữ liệu. Khi lệnh pg_dump được thực thi có thể xuất ra file SQL chứa cấu trúc của tất cả các bảng được tạo trong cơ sở dữ liệu cũng như dữ liệu được lưu trữ trong các bảng. Từ đó sử dụng thông tin cần thiết này để xây dựng lại cơ sở dữ liệu – restore database.

Xem thêm bài viết sử dụng lệnh pg_dump để: Sao chép cơ sở dữ liệu PostgreSQL từ máy chủ này sang máy chủ khác

Ở đây, chúng chỉ muốn xác nhận cấu trúc của một bảng cụ thể, vì vậy sẽ thực thi lệnh pg_dump có cú pháp như sau:

pg_dump -U role_name -t table_name -s database_name

Ví dụ sau login bằng role postgres để in ra toàn bộ định nghĩa, cấu trúc của table product trong database mydb:

pg_dump -U postgres -t product -s mydb

Trường hợp muốn xuất thông tin này vào file SQL dùng để backup dữ liệu để restore khi cần thì thêm đường dẫn của file SQL vào cuối của lệnh pg_dump như sau:

pg_dump -U role_name -t table_name -s database_name > path_file_sql

chẳng hạn muốn xuất thông tin vào file backup_db.sql thì:

pg_dump -U postgres -t product -s mydb > backup_db.sql

※Nếu chỉ để tên file như trên thì sẽ xuất file tại đường dẫn như dấu nhắc lệnh C:\Users\Admin

4. Lấy danh sách các cột có trong bảng

Để lấy danh sách các cột có trong bảng, chúng ta sử dụng lệnh sau:

¥d [schema_name.]table_name

Xem thêm: Đường dẫn tìm kiếm schema (lược đồ)

Ví dụ lấy thông tin cột của table_name tblstudents trong schema_name public (vì là mặc định nên được bỏ qua không chỉ định trong câu lệnh thực thi) của database sampledata: