自增主键和额外的用于记录行插入时的时间戳的字段,在关系型数据库表设计时算是两个很常规的字段了。之前一直用MySQL,定义起来很简单。近来接触PostgreSQL,其字段定义跟MySQL还是有区别的,这里记录一下PG的DDL写法。
CREATE TABLE "public"."my_pg_table" (
"id" BIGSERIAL NOT NULL,
"content" VARCHAR NULL DEFAULT NULL,
"yesterday" DATE NOT NULL DEFAULT DATE(TIMEZONE('UTC-8'::TEXT, NOW())) - INTEGER '1',
"ts" TIMESTAMP NOT NULL DEFAULT TIMEZONE('UTC-8'::TEXT, NOW()::TIMESTAMP(0) WITHOUT TIME ZONE),
PRIMARY KEY ("id")
)
;
“id”列是自增主键,使用PG定义的SERIAL数据类型。
“ts”列记录数据插入表中的时间戳,`TIMEZONE'函数定义时间戳的时区,`NOW'函数后面的修饰语是为了给日期时间数据省去其精度后缀而转换成“YYYY-MM-DD HH:MM:SS”的通用标准格式。
“yesterday”列其实是一个无意义的定义,因为工作中往往会用得到,所以主要是为了记录一下PG查询日期计算时前一天的写法:DATE(TIMEZONE('UTC-8'::TEXT, NOW())) - INTEGER '1'。
postgres=# INSERT INTO "public"."my_pg_table" ("content") VALUES ('a');
INSERT 0 1
postgres=# INSERT INTO "public"."my_pg_table" ("content") VALUES ('b');
INSERT 0 1
postgres=# INSERT INTO "public"."my_pg_table" ("content") VALUES ('c');
INSERT 0 1
postgres=# SELECT * FROM "public"."my_pg_table";
id | content | yesterday | ts
----+---------+------------+---------------------
1 | a | 2020-09-08 | 2020-09-09 19:23:12
2 | b | 2020-09-08 | 2020-09-09 19:23:12
3 | c | 2020-09-08 | 2020-09-09 19:23:13
(3 rows)