PostgreSQL如何设置一个带有时间戳的主键列，以自动填充。

Question

我该如何在PostgreSQL中实现这个功能？

我想要创建一个表，其中包含一个时间戳字段，它会在插入时自动获取now()的值。时间戳永远不会被更新。我认为我需要创建一个触发器来完成这个操作。如果有更简单的方法，请告知。

或者，将列设置为默认值为now，然后在没有指定时间戳列的情况下进行插入，以便它获取默认值？

创建表foo（q_i_time timestamp with time zone not null default now（），someval int）;

CREATE TABLE
billing=# insert into foo (someval) values (22);
INSERT 0 1
billing=# insert into foo (someval) values (26);
INSERT 0 1
billing=# insert into foo (someval) values (1);
INSERT 0 1
billing=# select * from foo;

           q_i_time            | someval
-------------------------------+---------
 2008-02-25 17:23:03.247619-08 |      22
 2008-02-25 17:23:07.43922-08  |      26
 2008-02-25 17:23:10.111189-08 |       1
(3 rows)

这个自动填充时间戳列可以作为主键吗？

谢谢提前。

- litu16

Answer 1

因为你没有更新时间戳，所以无需触发器。只需声明一个DEFAULT即可，例如：

CREATE TABLE demo (
    id integer primary key,
    created_at timestamp not null default current_timestamp
);

例子：

test=>     CREATE TABLE demo (
test(>         id integer primary key,
test(>         created_at timestamp not null default current_timestamp
test(>     );
CREATE TABLE
test=> INSERT INTO demo(id) VALUES(1),(2);
INSERT 0 2
test=> INSERT INTO demo(id) VALUES(3);
INSERT 0 1
test=> SELECT * FROM demo;
 id |         created_at         
----+----------------------------
  1 | 2015-06-13 12:30:55.169221
  2 | 2015-06-13 12:30:55.169221
  3 | 2015-06-13 12:30:57.395104
(3 rows)

请注意，由于这是一笔单一的交易，因此前两个时间戳是相同的。如果您不希望出现这种情况，请使用clock_timestamp而不是current_timestamp。

请注意，应用程序仍然可以通过明确指定时间戳来覆盖它：

test=> INSERT INTO demo(id, created_at) VALUES (4, '2014-01-01 00:00:00');
INSERT 0 1
test=> SELECT * FROM demo WHERE id = 4;
 id |     created_at      
----+---------------------
  4 | 2014-01-01 00:00:00
(1 row)

如果你想要强制值或者想在行更新时更新它，才需要触发器。

- Craig Ringer

---

@litu16 没有功能上的区别，但 current_timestamp 是 SQL 标准拼写。 - Craig Ringer

@litu16 关于问题中的“主键”部分...它可以是主键，但请记住时钟具有有限的分辨率，因此可能会出现重复导致错误。您还必须使用clock_timestamp作为默认值。我不建议使用捕获的时间戳作为主键。 - Craig Ringer

嗨，Craig。我在网上搜索有关将时间戳作为主键可能导致重复错误的情况，你是对的。谢谢。根据这项研究，我认为最好的选择是创建一个主键，里面包含串联其他列值的序列号，你有什么建议吗？ - litu16

@litu16 我会使用 serial。这是一种情况，自然键（时间戳）由于技术限制而不完美。特别是在 Windows 上，时间戳精度最多只有 1 毫秒（直到 PostgreSQL 9.5 在 Windows 8 上改进），因此冲突很可能发生。这正是像 serial 列这样的代理键适用的时候。 - Craig Ringer

嘿，我在Windows 8.1上使用PostgreSQL 9.4，我以为这是最新版本，不是吗？我应该尝试一下新的PostgreSQL发布吗？我听说序列也有其限制，我记得它是在位大小方面。 - litu16