将自动递增列添加到现有表中，并按日期排序。

Question

我有一个已存在于数据库中的名为"tickets"的表格，其中包含以下列:

id (string, Primary Key, contains UUID like e6c49164-545a-43a1-845f-73c5163962f2) 
date   (biginteger, stores epoch)
status (string)

我需要添加一个新的自增列ticket_id，但生成的值应根据“日期”列的值确定。

我尝试过这个：

ALTER TABLE "tickets" ADD COLUMN "ticket_id" SERIAL;

问题是，它以一些奇怪的顺序生成“ticket_id”值，似乎是基于表的主键“id”列。能否按照“日期”排序生成连续值？这很重要，因为“ticket_id”需要按照生成票据的顺序显示。

- Ashutosh

---

你能否考虑创建一个新表，并使用 INSERT INTO newtable SELECT ctid, ... FROM tickets WHERE ... ORDER BY date 插入正确的值？ - Yann39

不相关，但是：你为什么要使用bigint来表示时间戳？在SQL中使用一个真正的“timestamp”列会更容易处理。 - user330315

Answer 1

如果您添加了这样的序列列，现有行将自动以“任意”顺序更新。

要控制ID生成的顺序，您需要分多个步骤执行：

首先添加列而不带有默认值（serial意味着默认值）。

ALTER TABLE tickets ADD COLUMN ticket_id integer;

然后创建一个序列以生成这些值：

create sequence tickets_ticket_id_seq;

然后更新现有的行

update tickets 
  set ticket_id = t.new_id
from (
   select id, nextval('tickets_ticket_id_seq') as new_id
   from tickets
   order by "date"
) t
where t.id = tickets.id;

然后将该序列设置为新列的默认值

alter table tickets alter column ticket_id set default nextval('tickets_ticket_id_seq');

最后，将序列与列相关联（这也是 serial 在后台执行的操作）：

alter sequence tickets_ticket_id_seq owned by tickets.ticket_id;

如果表格非常大（数千万或数亿），那么创建一个新的表可能会更快：

create sequence tickets_ticket_id_seq;
create table tickets_new
as
select id, nextval('activities_ticket_id_seq') ticket_id, "date", status
from tickets
order by "date";

drop table tickets cascade;
alter table tickets_new rename to tickets;
alter table tickets add primary key (id);
alter sequence tickets_ticket_id_seq owned by tickets.ticket_id;

然后重新创建该表的所有外键和索引。

- user330315

---

非常好的回答，您能否将“activities”更新为“tickets”，这是我的错误，请多多关照。谢谢。 - Ashutosh