pgsql 自增主键使用

--创建序列

CREATE SEQUENCE "xiang_base"."cloud_new_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

SELECT setval('"xiang_base"."cloud_new_id_seq"', 1, true);

ALTER SEQUENCE "xiang_base"."cloud_new_id_seq"
OWNED BY "xiang_base"."cloud_news"."id";

ALTER SEQUENCE "xiang_base"."cloud_new_id_seq" OWNER TO "cloud_admin";

--修改表名

ALTER TABLE xiang_base.cloud_news RENAME TO cloud_news_bak_20221214;

--备份表
create table xiang_base.cloud_news_bak_20221214_1 as select * from xiang_base.cloud_news_bak_20221214;

--创建表，设置id为自增序列
CREATE TABLE "xiang_base"."cloud_news" (
    "id" int8 NOT NULL DEFAULT nextval('xiang_base.cloud_new_id_seq'::regclass),
    "title" TEXT COLLATE "pg_catalog"."default",
    "news_content" TEXT COLLATE "pg_catalog"."default",
    "news_content_m" TEXT COLLATE "pg_catalog"."default",
    "pic_path" VARCHAR ( 256 ) COLLATE "pg_catalog"."default",
    "intro" TEXT COLLATE "pg_catalog"."default",
    "is_deleted" bool NOT NULL DEFAULT FALSE,
    "creator" VARCHAR ( 32 ) COLLATE "pg_catalog"."default",
    "updater" VARCHAR ( 32 ) COLLATE "pg_catalog"."default",
    "create_time" TIMESTAMP ( 3 ) DEFAULT now( ),
    "update_time" TIMESTAMP ( 3 ) DEFAULT now( ),
    "description" TEXT COLLATE "pg_catalog"."default",
    "keywords" TEXT COLLATE "pg_catalog"."default",
    CONSTRAINT "cloud_news_pkey1" PRIMARY KEY ( "id" ) 
);
ALTER TABLE "xiang_base"."cloud_news" OWNER TO "cloud_admin";
COMMENT ON COLUMN "xiang_base"."cloud_news"."title" IS '新闻标题';
COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content" IS '新闻内容';
COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content_m" IS '新闻移动端内容';
COMMENT ON COLUMN "xiang_base"."cloud_news"."pic_path" IS '新闻图片';
COMMENT ON COLUMN "xiang_base"."cloud_news"."intro" IS '介绍';
COMMENT ON COLUMN "xiang_base"."cloud_news"."is_deleted" IS '删除标志:FALSE:未删除,TRUE:已删除';
COMMENT ON COLUMN "xiang_base"."cloud_news"."creator" IS '创建人';
COMMENT ON COLUMN "xiang_base"."cloud_news"."updater" IS '更新人';
COMMENT ON COLUMN "xiang_base"."cloud_news"."create_time" IS '创建时间';
COMMENT ON COLUMN "xiang_base"."cloud_news"."update_time" IS '更新时间';
COMMENT ON COLUMN "xiang_base"."cloud_news"."description" IS '描述';
COMMENT ON COLUMN "xiang_base"."cloud_news"."keywords" IS '关键词';
----------------------------------------------------
--修改表id
UPDATE xiang_base.cloud_news_bak_20221214_1 SET id = '1' WHERE id = 'dhfsjdfhwlkhfeowiehfowe';
UPDATE xiang_base.cloud_news_bak_20221214_1 SET id = '2' WHERE id = 'dhfsjdfhwlkhfeowiehfowe';
UPDATE xiang_base.cloud_news_bak_20221214_1 SET id = '3' WHERE id = 'dhfsjdfhwlkhfeowiehfowe';
----------------------------------------------------

--从一张表复制数据到另一张表
INSERT INTO xiang_base.cloud_news ( ID, title, news_content, news_content_m, pic_path, intro, is_deleted, creator, updater, create_time, update_time ) SELECT ID
:: int8,
title,
news_content,
news_content_m,
pic_path,
intro,
is_deleted,
creator,
updater,
create_time,
update_time 
FROM
    xiang_base.cloud_news_bak_20221214_1;