数据库实践——瀚高数据库设置自增字段

数据库实践——瀚高数据库设置自增字段

方式一： 使用SERIAL

create table tablename ( colname serial );

等同于

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

方式二： 创建自增序列（起始值必须大于 0 ）

CREATE SEQUENCE event_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;   

主键 ID 默认设置为

alter table event alter column id set default nextval('event_id_seq');

方式三： 创建function

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

创建 trigger

CREATE TRIGGER user_seq BEFORE INSERT ON user FOR EACH ROW WHEN (NEW.id IS NULL) EXECUTE PROCEDURE user();  

创建seq

CREATE SEQUENCE user_seq OWNED BY user.id;

返回自增id的值

insert into users (name,gender,age,remark) values (#{name},#{gender},#{age},#{remark}) returning id