postgresql-自增字段

标识列Identity

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

-- 自动生成序列
CREATE SEQUENCE public.t_user_user_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
1
2
3
4
5
6
7
8

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

-- 查询数据
select * from t_user;
1
2

Serial类型

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

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

-- 查询数据
select * from t_user;
1
2

Sequence序列

-- 先创建序列
create sequence public.t_user_user_id_seq
	increment by 1
	minvalue 1
	maxvalue 2147483647
	start 1
	cache 1
	no cycle;
1
2
3
4
5
6
7
8

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

-- 表创建成功的ddl

CREATE 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)
);
1
2
3
4
5
6
7
8

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

-- 查询数据
select * from t_user;
1
2