postgresql-自增字段

标识列Identity

-- 测试表
create table t_user(
-- 标识列自增字段user_id integer generated always as identity primary key,user_name varchar(50) not null unique
);

-- 自动生成序列
CREATE SEQUENCE public.t_user_user_id_seqINCREMENT BY 1MINVALUE 1MAXVALUE 2147483647START 1CACHE 1NO CYCLE;

-- 插入数据
insert into t_user (user_name) values('tony'),('tom'),('ben');

-- 查询数据
select * from t_user;

Serial类型

create table t_user(
-- 自增字段user_id serial primary key,user_name varchar(50) not null unique
);

-- 插入数据
insert into t_user (user_name) values('tony'),('tom'),('ben');

-- 查询数据
select * from t_user;

Sequence序列

-- 先创建序列
create sequence public.t_user_user_id_seqincrement by 1minvalue 1maxvalue 2147483647start 1cache 1no cycle;

create table t_user(
-- 序列自增字段user_id integer default nextval('t_user_user_id_seq') primary key,user_name varchar(50) not null unique
);

-- 表创建成功的ddlCREATE TABLE public.t_user (user_id serial4 NOT NULL,user_name varchar(50) NOT NULL,CONSTRAINT t_user_pkey PRIMARY KEY (user_id),CONSTRAINT t_user_user_name_key UNIQUE (user_name)
);

-- 插入数据
insert into t_user (user_name) values('tony'),('tom'),('ben');

-- 查询数据
select * from t_user;