PostgreSQL建表操作

• 1、PostgreSQL序列操作

  • 创建ID序列

      CREATE SEQUENCE dwd_test_id_seq START 1;
  1

  • 重置ID序列

      ALTER SEQUENCE dwd_test_id_seq RESTART WITH 1;
  1

  • 查看ID当前值

      SELECT last_value FROM dwd_test_id_seq;
  1

• 2、PostgreSQL触发器函数

create or replace function cs_timestamp() returns trigger as
$$
begin
    new.updatetime= current_timestamp;
    return new;
end
$$
language plpgsql;
1
2
3
4
5
6
7
8

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

  序列变量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 '是否进行逻辑删除';
  1
  2
  3
  4
  5
  6

  注意：这里是COMMENT ON COLUMN

  • 添加表注释

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

  注意：这里是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
      );
  1
  2
  3
  4
  5
  6

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

  • 添加时间触发器

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

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

  • 添加主键信息

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

• 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";
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
)
;
-- ----------------------------
-- 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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

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

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