Sau khi install PostgreSQL việc đầu tiên bạn phải làm là tạo ra một database cluster để thực hiện tiếp các truy vấn (SQL) khác. Để tạo một database cluster, ta sử dụng command initdb. Sau đó khởi động PostgreSQL là ta có thể bắt đầu sử dụng PostgreSQL. Bạn có băn khoăn tại sao mình lại có thể thực hiện được truy vấn, mình đang access bằng User nào truy cập vào database nào để thực hiện truy vấn, ... Bài viết này sẽ giới thiệu một số Objects chính trên PostgreSQL, cách tạo ra chúng, và cách nhìn tổng quan về các Objects lưu trữ dữ liệu trên PostgreSQL.

Một database cluster được tạo và khởi động (dựa trên thông tin bạn nhập trên wizard của installer) nếu bạn cài đặt PostgreSQL qua installer trên Windows.

Khái quát về các Objects lưu trữ trên PostgreSQL

Giống như các RDBMS khác, bảng dữ liệu là nơi PostgreSQL thực hiện lưu trữ dữ liệu. Bảng dữ liệu được lưu trữ trong đơn vị lớn hơn là database, các database nằm bên dưới đơn vị lớn nhất của PostgreSQL là database cluster. Như hình minh họa bên dưới ta còn thấy bảng dữ liệu nằm dưới schema và tablespace. Schema là đơn vị tập hợp các bảng (và một số objects khác) theo hình thức logic. Và tablespace là đơn vị của một tập hợp các bảng và indexes theo hình thức vật lý. Xin vui lòng chi xem tiết các Objects lưu trữ như bên dưới.

Database cluster

Là đơn vị lưu trữ lớn nhất của một PostgreSQL database server. Database cluster được tạo ra bởi câu lệnh initdb, bao gồm các files config (postgresql.conf, pg_hba.conf, ...), và tất cả các đối tượng lưu trữ đều nằm trong database cluster. Xin vui lòng xem chi tiết trong bài viết Cấu trúc thư mục PostgreSQL.

Có thể cài đặt nhiều PostgreSQL database server trên một hệ điều hành, bằng cách chỉ định các port (thiết lập trong $PGDATA/postgresql.conf) khác nhau cho mỗi server.

Database

Là đơn vị lớn sau Database cluster. Để thực hiện được câu truy vấn, bạn phải truy cập vào một database nào đó. Mặc định sau khi tạo database cluster, PostgreSQL tạo ra 3 database như bên dưới.

 
$ psql
Timing is on.
psql (10.3)
Type "help" for help.

10300 postgres@postgres=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 postgres  | bocap | UTF8     | C       | C     | 
 template0 | bocap | UTF8     | C       | C     | =c/bocap         +
           |       |          |         |       | bocap=CTc/bocap
 template1 | bocap | UTF8     | C       | C     | =c/bocap         +
           |       |          |         |       | bocap=CTc/bocap
(3 rows)

PostgreSQL cũng như các RDBMS khác, để truy nhập một database. Ta cần thông tin: tên database, tên user, password(ở chứng thực trust thì bạn không cần), số port (cho từng database cluster), hostname or IP address.

Để tạo một database ta sử dụng câu lệnh SQL "CREATE DATABASE", hoặc sử dụng createdb từ installer.

Khác với Oracle, PostgreSQL mặc định đối tượng câu lệnh SQL là chữ thường. Nếu muốn chữ in hoa vui lòng viết đối tượng trong dấu "".

• Tạo database từ câu lệnh SQL

1. Cú pháp

10300 postgres@postgres=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

2. Ví dụ

10300 postgres@postgres=# CREATE DATABASE testdb TEMPLATE template0;
CREATE DATABASE
Time: 4941.826 ms (00:04.942)
10300 postgres@postgres=# \l
                           List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+---------+-------+-------------------
 postgres  | bocap    | UTF8     | C       | C     | 
 template0 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 template1 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

10300 postgres@postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
10300 postgres@testdb=# \d
Did not find any relations.
10300 postgres@testdb=# \c postgres 
You are now connected to database "postgres" as user "postgres".
10300 postgres@postgres=# DROP DATABASE testdb ;
DROP DATABASE

Để hiện thị thông tin của database trên psql mode, ta sử dụng meta command \l hay \l+ (chi tiết). Muốn access vào database nào đó ta sử dụng meta command "\c database".

• Tạo database từ câu lệnh createdb

1. Cú pháp

 
$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

2. Ví dụ

 
[ ~]$ createdb --template=template0 testdb
[ ~]$ psql -l
Timing is on.
                           List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+---------+-------+-------------------
 postgres  | bocap    | UTF8     | C       | C     | 
 template0 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 template1 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

[ ~]$ dropdb testdb
[ ~]$

Schema

Là đơn vị lưu trữ bên dưới database, quản lý dữ liệu dưới dạng logic. Mặc định trong mỗi database có một schema cho người sử dụng, đó là schema public. Ta có thể tạo schema bằng câu lệnh "CREATE SCHEMA ". Đặc điểm của 1 schema như bên dưới.

• Có thể sử dụng tên trùng với schema ở database khác but không trùng tên trên cùng database.
• Ngoài TABLESPACE và user ra, schema có thể chứa hầu hết các đối tượng còn lại (như table, index, sequence, constraint...)
• để truy cập schema ta có thể thêm tên schema vào phía trước đối tượng muốn truy cập hoặc sử dụng tham số search_path để thay đổi schema truy cập hiện tại.
• Schema có thể sử dụng với các mục đích như tăng cường security, quản lý dữ liệu dễ dàng hơn.

Khác với Oracle, Schema của PostgreSQL không có liên quan tới database User.

Để tạo schema, ta phải truy cập vào một database nào đó rồi thực hiện lệnh "CREATE SCHEMA " để tạo.

1. Cú pháp

10300 postgres@postgres=# \h CREATE SCHEMA
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

2. Ví dụ:

10300 postgres@postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

10300 postgres@postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

10300 postgres@postgres=# create schema testscm;
CREATE SCHEMA
10300 postgres@postgres=# create table testscm.testtbl();
CREATE TABLE
10300 postgres@postgres=# \d
Did not find any relations.
10300 postgres@postgres=# set search_path to testscm ;
SET
10300 postgres@postgres=# \d
          List of relations
 Schema  |  Name   | Type  |  Owner
---------+---------+-------+----------
 testscm | testtbl | table | postgres
(1 row)

10300 postgres@postgres=# \dn
  List of schemas
  Name   |  Owner
---------+----------
 public  | postgres
 testscm | postgres
(2 rows)

10300 postgres@postgres=#

TABLESPACE

Là đơn vị lưu trữ dữ liệu về phương diện vật lý bên dưới database. Thông thường dữ liệu vật lý được lưu trữ tại thư mục dữ liệu (nơi ta chỉ định lúc ta tạo database cluster initdb -D). Nhưng có một phương pháp lưu trữ dữ liệu ngoài phân vùng này, nhờ sử dụng chức năng TABLESPACE. Khi tạo một TABLESPACE tức là ta đã tạo ra một vùng lưu trữ dữ liệu mới độc lập với dữ liệu bên dưới thư mục dữ liệu. Điều này giảm thiểu được disk I/O cho phân vùng thư mục dữ liệu (nếu trong các hệ thống cấu hình RAID, hay hệ thống có 1 đĩa cứng thì không có hiệu quả).

1. Cú pháp

10300 postgres@postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

10300 postgres@postgres=#

2. Ví dụ

10300 postgres@postgres=# \! mkdir /tmp/testtablespace
10300 postgres@postgres=# create tablespace testtps location '/tmp/testtablespace';
CREATE TABLESPACE
10300 postgres@postgres=# \! ls -l /tmp/testtablespace/*
total 0
10300 postgres@postgres=# set default_tablespace to testtps;
SET
10300 postgres@postgres=# create table testtbl as select generate_series(1,10) as id;
SELECT 10
10300 postgres@postgres=# checkpoint;
CHECKPOINT
drwx------. 2 postgres postgres 18 Apr 15 13:59 13211
10300 postgres@postgres=# \! ls -l /tmp/testtablespace/*/*
total 8
-rw-------. 1 postgres postgres 8192 Apr 15 13:59 16389
10300 postgres@postgres=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16388 | testtps    |       10 |        |
(3 rows)

10300 postgres@postgres=# \! ls -l $PGDATA/pg_tblspc/16388
lrwxrwxrwx. 1 postgres postgres 19 Apr 15 13:58 /usr/local/pgsql/pg1000/data/pg_tblspc/16388 -> /tmp/testtablespace
10300 postgres@postgres=#

PostgreSQL tạo một symblic link có tên là oid (Object ID) của tablespace vừa được tạo ở bên dưới pg_tblspc của database cluster. Trỏ tới vùng dữ liệu được chỉ định (LOCATION) trong lúc tạo tablespace.

Để chuyển đổi qua các tablespace để lưu trữ dữ liệu, ta chỉ định thông qua parameter default_tablespace trước khi tạo đối tượng (bảng hoặc index). Ngoài ra đối với bảng tạm thời (temporary table) và index của nó, để chuyển đổi vùng lưu trữ ta thiết lập tablespace cho tham số temp_tablespaces trước khi tạo đối tượng. default_tablespace và temp_tablespaces đều có giá trị mặc định là không thiết lập, tức là sử dụng tablespace mặc định bên dưới database cluster.

ví dụ trên sử dụng meta command ! của psql, meta command này thực hiện các lệnh shell bên ngoài.

Bảng(table)

Bảng là đối tượng lưu trữ dữ liệu từ người dùng. Một bảng bao gồm 0 hoặc nhiều cột (column) tương ứng với từng kiểu dữ liệu khác nhau của PostgreSQL.
Tổng quan có 3 kiểu tables mà PostgreSQL support, đó là

• unlogged table: là kiểu table mà các thao tác đối với bảng dữ liệu này không được lưu trữ vào WAL. Tức là không có khả năng phục hồi nếu bị corrupt.
• temporary table: là kiểu table chỉ được tạo trong phiên làm việc đó. Khi connection bị ngắt, nó sẽ tự động mất đi.
• table thông thường: Khác với 2 kiểu table trên, là loại table thông thường để lưu trữ dữ liệu. Có khả năng phục hồi khi bị corrupt và tồn tại vĩnh viễn nếu không có thao tác xóa bỏ nào.

PostgreSQL support nhiều kiểu dữ liệu, bao gồm cả kiểu dữ liệu do người dùng định nghĩa. Để biết thêm chi tiết vui lòng  thao khảo ở đây

1. Cú pháp tạo

postgres=# \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] FOR VALUES partition_bound_spec
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and like_option is:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }

and partition_bound_spec is:

IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

2. Ví dụ:

postgres=#
postgres=# create unlogged table test_unlog_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create temporary table test_temp_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create table test_nomarl_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# \d
               List of relations
  Schema   |      Name       | Type  |  Owner
-----------+-----------------+-------+----------
 pg_temp_3 | test_temp_tbl   | table | postgres
 public    | test_nomarl_tbl | table | postgres
 public    | test_unlog_tbl  | table | postgres
(3 rows)

postgres=# \q
[postgres@localhost ~]$ psql
psql (10.3)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | test_nomarl_tbl | table | postgres
 public | test_unlog_tbl  | table | postgres
(2 rows)

postgres=#

index

Index là đối tượng chỉ mục của một cột nào đó trong bảng, đối tượng này đã được sắp xếp theo một trình tự nào đó khi được sử dụng có thể làm tăng khả năng tìm kiếm.
PostgreSQL hiện tại(tới phiên bản 10) support 6 loại index btree, hash, gist, spgist, gin, and brin. Thường thì btree và hash là 2 loại index được sử dụng rộng rãi. Các index còn lại được sử dụng cho từng mục đích khác nhau, ví dụ gist và spgist cho kiểu dữ liệu liên quan tới vị trí địa lý, gin cho kiểu dữ liệu text tìm kiếm với dữ liệu lớn, brin được thiết kế để xử lý các bảng lớn, trong đó các cột nhất định có một số tương quan tự nhiên với vị trí thực của chúng trong bảng.

1. Cú pháp tạo

postgres=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

postgres=#

2. Ví dụ

postgres=# create index test_nomarl_tbl_idx on test_nomarl_tbl using btree (id);
CREATE INDEX
postgres=# create index test_nomarl_tbl_hash_idx on test_nomarl_tbl using hash (id);
CREATE INDEX
postgres=# \d test_nomarl_tbl
          Table "public.test_nomarl_tbl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
Indexes:
    "test_nomarl_tbl_hash_idx" hash (id)
    "test_nomarl_tbl_idx" btree (id)
Tablespace: "testtps"

postgres=#

Chi tiết về các loại index xin tìm hiểu ở đây: https://www.postgresql.org/docs/current/static/internals.html