GreenPlum或PostgreSQL的建表操作

最近发现了id序列函数nextval的一种平替写法，置顶一下。

serial <=> int4 <=> nextval('"dwd".dwd_test_id_seq'::regclass)
bigserial <=> int8 <=> nextval('"dwd".dwd_test_id_seq'::regclass)

  DROP TABLE IF EXISTS "dwd"."dwd_test";
  CREATE TABLE "dwd"."dwd_test" (
  	"id" serial NOT NULL, -- 如果是int8，那么这里要改为 bigserial
    -- "id" int8 NOT NULL DEFAULT nextval('"dwd".dwd_test_id_seq'::regclass),
    "trace_id" int8 NOT NULL,
    "last_modified_time" int8,
    "insert_time" timestamp(0) DEFAULT now(),
    "update_time" timestamp(0) DEFAULT now(),
    "is_deleted" bool,
    CONSTRAINT dwd_test_pkey PRIMARY KEY (id)
  );

分割线

====================================================================

• 1、PostgreSQL序列操作

  • 创建ID序列

      CREATE SEQUENCE dwd_test_id_seq START 1;
  

  • 重置ID序列

      ALTER SEQUENCE dwd_test_id_seq RESTART WITH 1;
  

  • 查看ID当前值

      SELECT last_value FROM dwd_test_id_seq;
  

• 2、PostgreSQL触发器函数

create or replace function cs_timestamp() returns trigger as
$$
begin
    new.updatetime= current_timestamp;
    return new;
end
$$
language plpgsql;

• 3、PostgreSQL建表语句

  • 新建一张表，如果表已经存在，则先删除该表再重新建立该表。

      DROP TABLE IF EXISTS "dwd"."dwd_test";
      CREATE TABLE "dwd"."dwd_test" (
        "id" int8 NOT NULL DEFAULT nextval('"dwd".dwd_test_id_seq'::regclass),
        "trace_id" int8 NOT NULL,
        "last_modified_time" int8,
        "insert_time" timestamp(6) DEFAULT now(),
        "update_time" timestamp(6) DEFAULT now(),
        "is_deleted" bool
      );
  

  序列变量dwd_test_id_seq需要在建表之前完成，否则建表会报错：找不到变量dwd_test_id_seq。插入时间insert_time和更新时间update_time需要设置为自动更新，可以用函数now()完成。另外，更新时间update_time需要进一步设置为根据记录字段变动而变动的状态，与MySQL不同，我们无法直接在数据库的表格设计里完成设置，需要借助触发器完成。

  • 添加列注释

      COMMENT ON COLUMN "dwd"."dwd_test"."id" IS '自增id';
      COMMENT ON COLUMN "dwd"."dwd_test"."trace_id" IS '上游表id';
      COMMENT ON COLUMN "dwd"."dwd_test"."last_modified_time" IS '时间戳';
      COMMENT ON COLUMN "dwd"."dwd_test"."insert_time" IS '插入时间';
      COMMENT ON COLUMN "dwd"."dwd_test"."update_time" IS '更新时间';
      COMMENT ON COLUMN "dwd"."dwd_test"."is_deleted" IS '是否进行逻辑删除';
  

  注意：这里是COMMENT ON COLUMN

  • 添加表注释

      COMMENT ON TABLE "dwd"."dwd_test" IS '测试表';
  

  注意：这里是COMMENT ON TABLE

  • 添加索引信息

      CREATE INDEX "idx_test_id" ON "dwd"."dwd_test" USING btree (
        "id" "pg_catalog"."int8_ops" ASC NULLS LAST
      );
      CREATE INDEX "idx_test_lmt" ON "dwd"."dwd_test" USING btree (
        "last_modified_time" "pg_catalog"."int8_ops" ASC NULLS LAST
      );
  

  注意：这里是 "pg_catalog"及之后的内容可以删掉，PostgreSQL会自动帮你补充完整。

  • 添加时间触发器

      CREATE TRIGGER "dwd_test_trigger" BEFORE UPDATE ON "dwd"."dwd_test"
      FOR EACH ROW
      EXECUTE PROCEDURE "dwd"."cs_timestamp"();
  

  对表的每一行数据都执cs_timestamp()函数操作，只要这一行的某个字段发生变化，那么update_time这个字段就会相应发生变化，记录当前更新的时间。

  • 添加主键信息

      ALTER TABLE "dwd"."dwd_test" ADD CONSTRAINT "dwd_test_pkey" PRIMARY KEY ("id");
  

• 4、完整的建表语句如下：

/*
 Source Server Type    : PostgreSQL
 File Encoding         : 65001

 Date: 10/08/2022 13:45:03
*/

-- ----------------------------
-- Table structure for dwd_test
-- ----------------------------
DROP TABLE IF EXISTS "dwd"."dwd_test";
-- 注意：SEQUENCE 这两步要放在删表和创建表之间
DROP SEQUENCE IF EXISTS "dwd"."dwd_test_id_seq";
CREATE SEQUENCE "dwd"."dwd_test_id_seq" START WITH 1;
CREATE TABLE "dwd"."dwd_test" (
  "id" int8 NOT NULL DEFAULT nextval('"dwd".dwd_test_id_seq'::regclass),
  "trace_id" int8 NOT NULL,
  "last_modified_time" int8,
  "insert_time" timestamp(6) DEFAULT now(),
  "update_time" timestamp(6) DEFAULT now(),
  "is_deleted" bool
)
DISTRIBUTED BY (id)
;
-- ----------------------------
-- Column comment for dwd_test
-- ----------------------------
COMMENT ON COLUMN "dwd"."dwd_test"."id" IS '自增id';
COMMENT ON COLUMN "dwd"."dwd_test"."trace_id" IS '上游表id';
COMMENT ON COLUMN "dwd"."dwd_test"."last_modified_time" IS '时间戳';
COMMENT ON COLUMN "dwd"."dwd_test"."insert_time" IS '插入时间';
COMMENT ON COLUMN "dwd"."dwd_test"."update_time" IS '更新时间';
COMMENT ON COLUMN "dwd"."dwd_test"."is_deleted" IS '是否进行逻辑删除';
-- ----------------------------
-- Table comment for dwd_test
-- ----------------------------
COMMENT ON TABLE "dwd"."dwd_test" IS '测试表';


-- ----------------------------
-- Indexes structure for table dwd_test
-- ----------------------------
CREATE INDEX "idx_test_id" ON "dwd"."dwd_test" USING btree (
  "id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_test_lmt" ON "dwd"."dwd_test" USING btree (
  "last_modified_time" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Triggers structure for table dwd_test
-- ----------------------------
CREATE TRIGGER "dwd_test_trigger" BEFORE UPDATE ON "dwd"."dwd_test"
FOR EACH ROW
EXECUTE PROCEDURE "dwd"."cs_timestamp"();

-- ----------------------------
-- Primary Key structure for table dwd_test
-- ----------------------------
ALTER TABLE "dwd"."dwd_test" ADD CONSTRAINT "dwd_test_pkey" PRIMARY KEY ("id");

注意：步骤1和步骤2需要在建表语句前执行，但这两个步骤之间没有先后关系。

https://blog.csdn.net/J926926/article/details/109173738