postgres序列mysql_【数据库】postgresql数据库创建自增序列id的注意事项

1.创建一张表

CREATE TABLE "public"."tt" (

"name" varchar(128),

"status" int4 DEFAULT 2,

"id" int8 NOT NULL,

CONSTRAINT "tt_pkey" PRIMARY KEY ("id")

)

WITH (OIDS=FALSE)

;

ALTER TABLE "public"."tt" OWNER TO "postgres";

2.创建与表对应的自增序列

CREATE SEQUENCE tt_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE public.tt_id_seq OWNER TO postgres;

#最重要的，不执行下面这句的话，导出的sql.json中id的sequence值会为null

ALTER SEQUENCE tt_id_seq OWNED BY tt.id;

ALTER table tt  ALTER column id set default nextval('tt_id_seq');

SELECT pg_catalog.setval('tt_id_seq', 1, false);

发现sequence不存在的情况

解决思路：删除相关表和依赖进行重建

---补充(绕了一大圈，从《PostgreSQL修炼之道》可以发现)

序列类型

CREATETABLE t (id SERIAL);

等价于

CREATE SEQUENCE t_id_seq;

CREATE TABLE t ( id integer NOT NULL DEFAULT nextval(‘t_id_seq’) );

ALTER SEQUENCE t_id_seq OWNED BY t_id;

---------------------