PostgreSQL 其他兼容特性

自增列与序列

PostgreSQL 模式支持 SERIAL 自增列。

postgres=# CREATE TABLE halo_test (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE

postgres=# INSERT INTO halo_test (name) VALUES ('Alice'), ('Bob');
INSERT 0 2

postgres=# SELECT * FROM halo_test;
 id | name  
----+-------
  1 | Alice
  2 | Bob
(2 行记录)

postgres=# DROP TABLE halo_test;
DROP TABLE

约束

支持 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL 等约束。

postgres=# CREATE TABLE halo_test (id INT PRIMARY KEY, name TEXT UNIQUE, age INT CHECK (age > 0));
CREATE TABLE

postgres=# INSERT INTO halo_test VALUES (1, 'Alice', 25);
INSERT 0 1

postgres=# SELECT * FROM halo_test;
 id | name  | age 
----+-------+-----
  1 | Alice |  25
(1 行记录)

postgres=# DROP TABLE halo_test;
DROP TABLE

外键约束保证引用完整性：

postgres=# CREATE TABLE parent_table (id INT PRIMARY KEY);
CREATE TABLE

postgres=# CREATE TABLE child_table (id INT PRIMARY KEY, parent_id INT REFERENCES parent_table(id));
CREATE TABLE

postgres=# INSERT INTO parent_table VALUES (1);
INSERT 0 1

postgres=# INSERT INTO child_table VALUES (1, 1);
INSERT 0 1

postgres=# SELECT * FROM parent_table JOIN child_table ON parent_table.id = child_table.parent_id;
 id | id | parent_id 
----+----+-----------
  1 |  1 |         1
(1 行记录)

postgres=# DROP TABLE child_table, parent_table;
DROP TABLE

权限管理

支持 PostgreSQL 标准的角色与权限管理。

postgres=# CREATE TABLE my_table (id NUMERIC, name VARCHAR(50));
CREATE TABLE

postgres=# CREATE ROLE role1;
CREATE ROLE

postgres=# GRANT SELECT ON my_table TO role1;
GRANT

postgres=# DROP TABLE my_table CASCADE;
DROP TABLE

触发器

支持 BEFORE、AFTER、INSTEAD OF 行级触发器、语句级触发器和事件触发器。

postgres=# CREATE TABLE testtr_log (id INT, name TEXT);
CREATE TABLE

postgres=# CREATE OR REPLACE FUNCTION trig_fn() RETURNS TRIGGER AS $$
BEGIN RAISE NOTICE 'BEFORE Trigger fired'; RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# CREATE TRIGGER before_insert BEFORE INSERT ON testtr_log FOR EACH ROW EXECUTE FUNCTION trig_fn();
CREATE TRIGGER

postgres=# INSERT INTO testtr_log VALUES (1, 'test');
注意:  BEFORE Trigger fired
INSERT 0 1

postgres=# DROP TABLE testtr_log CASCADE;
DROP TABLE

AFTER 触发器在数据写入后执行：

postgres=# CREATE TABLE employees (id INT, name TEXT);
postgres=# CREATE TABLE audit_log (id INT, name TEXT);
CREATE TABLE

postgres=# CREATE OR REPLACE FUNCTION after_trig_fn() RETURNS TRIGGER AS $$
postgres-# BEGIN
postgres-#   INSERT INTO audit_log VALUES (NEW.id, 'audit_'||NEW.name);
postgres-#   RETURN NEW;
postgres-# END; $$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# CREATE TRIGGER after_insert AFTER INSERT ON employees
postgres-# FOR EACH ROW EXECUTE FUNCTION after_trig_fn();
CREATE TRIGGER

postgres=# INSERT INTO employees VALUES (1, 'Alice');
INSERT 0 1

postgres=# SELECT * FROM employees;
 id | name  
----+-------
  1 | Alice
(1 行记录)

postgres=# DROP TABLE employees, audit_log CASCADE;
DROP TABLE

表连接

支持内连接、外连接和全连接。

postgres=# CREATE TABLE t_a (id INT, name TEXT);
postgres=# CREATE TABLE t_b (id INT, val TEXT);
postgres=# INSERT INTO t_a VALUES (1,'A'),(2,'B');
postgres=# INSERT INTO t_b VALUES (1,'AA');
postgres=# SELECT * FROM t_a LEFT JOIN t_b ON t_a.id = t_b.id;
 id | name | id | val 
----+------+----+-----
  1 | A    |  1 | AA
  2 | B    |    | 
(2 行记录)

postgres=# DROP TABLE t_a, t_b;
DROP TABLE

TRUNCATE

TRUNCATE 用于快速清空表中所有数据。

postgres=# CREATE TABLE testtr_log (id INT PRIMARY KEY, name TEXT);
CREATE TABLE

postgres=# INSERT INTO testtr_log VALUES (1, 'test');
INSERT 0 1

postgres=# SELECT count(*) FROM testtr_log;
 count 
-------
     1
(1 行记录)

postgres=# TRUNCATE TABLE testtr_log;
TRUNCATE TABLE

postgres=# SELECT count(*) FROM testtr_log;
 count 
-------
     0
(1 行记录)

postgres=# DROP TABLE testtr_log;
DROP TABLE

字符集

Halo 数据库支持 UTF-8、GB18030 等常用字符集。

postgres=# SHOW server_encoding;
 server_encoding 
-----------------
 UTF8
(1 行记录)