postgres中通过触发器实现自增列

时间：2022-07-11 08:14:13


对于自增列，SQLServer中有identity属性。MySQL中有auto_increment

对于postgresql中，应该如何实现呢？

postgresql的manual给了serial类型

CREATE TABLE tablename1 (
colname SERIAL
);

或者

CREATE SEQUENCE tablename2_colname_seq;
CREATE TABLE tablename2 (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename2_colname_seq OWNED BY tablename2.colname;

通过查看表的定义可知，上述两种方式的效果完全一致。serial类型其实就是interger的基础上添加了某些属性而已。

postgres=# \d tablename1
                            Table "public.tablename1"
 Column  |  Type   |                          Modifiers                           
---------+---------+--------------------------------------------------------------
 colname | integer | not null default nextval('tablename1_colname_seq'::regclass)

postgres=# \d tablename2
                            Table "public.tablename2"
 Column  |  Type   |                          Modifiers                           
---------+---------+--------------------------------------------------------------
 colname | integer | not null default nextval('tablename2_colname_seq'::regclass)


用serial实现的自增列通过设置defalut属性值的方式实现自增。所以，一旦用户对自增列显示的插入值（无论任何值），default属性都不会再起作用。

这个式样本身没有什么问题，但有部分用户希望 向自增列显示的插入NULL值时，自增列也能插入default的值而不是报错。

例： 

postgres=# insert into tablename1 values(null);
ERROR:  列"colname"内のNULL値はNOT NULL制約違反です (10284)
DETAIL:  失敗した行は(null)を含みます


为满足在显示的插入null值时，自增列能插入default的值而不是报错，可以通过触发器的方式实现自增列功能。

实现代码如下：

CREATE SEQUENCE SampleTbl_ID_seq;
CREATE TABLE SampleTbl (ID integer NOT NULL primary key , ITEM_1  NVARCHAR (20)   COLLATE "C",ITEM_2 DATE NOT NULL, ITEM_3 BOOLEAN DEFAULT('t'));

CREATE OR REPLACE FUNCTION check_id_insert() RETURNS trigger AS $BODY$
BEGIN
    NEW.ID := nextval('SampleTbl_ID_seq');
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE TRIGGER check_insert
    BEFORE INSERT ON SampleTbl
    FOR EACH ROW
    WHEN (NEW.ID IS NULL)
    EXECUTE PROCEDURE check_id_insert();

ALTER SEQUENCE SampleTbl_ID_seq OWNED BY SampleTbl.ID;


向表sampletbl的id列插入null时，id列自动插入sequence的值。 

postgres=# insert into sampletbl values(null,'cf','1988-09-20',true);
INSERT 0 1
postgres=# select * from sampletbl;
 id | item_1 |   item_2   | item_3 
----+--------+------------+--------
  1 | cf     | 1988-09-20 | t
(1 rows)


有关postgresql的触发器功能，可以参照以下内容：

http://blog.csdn.net/neo_liu0000/article/details/6255623

http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html


标签：

相关文章