将自动增量列添加到按日期排序的现有表中 [英] Add auto increment column to existing table ordered by date

问题描述

我在数据库中有一个名为"tickets"的现有表，其中包含列:

I have an existing table named "tickets" in database with columns:

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

我需要添加新的自动递增列 ticket_id ，但要生成的值应根据日期"列的值.

I need to add new auto increment column ticket_id but the values to be generated should be according to "date" column value.

我尝试过:

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

问题是，它正在以某种奇怪的顺序生成"ticket_id"值，看起来它基于表的主键"id"列.

The problem is, it is generating "ticket_id" values in some weird order, looks like it is based on "id" column which is the primary key of the table.

是否可以生成根据日期"排序的序列值?这很重要，因为需要根据票证的生成顺序显示"ticket_id".

Is it possible to generate serial values sorted according to "date"? It is important as "ticket_id" is required to be displayed according to order in which tickets were generated.

推荐答案

如果添加这样的串行列，则现有行将自动以任意"形式更新.订单.

If you add a serial column like that, the existing rows will automatically be updated in an "arbitrary" order.

要控制生成ID的顺序，您需要分多个步骤进行操作:

To control the order in which the IDs are generated you need to do this in multiple steps:

首先添加没有列的默认值( serial 表示默认值)

First add the column without a default (serial implies a default value)

ALTER TABLE tickets ADD COLUMN ticket_id integer;

然后创建一个序列以生成值:

Then create a sequence to generate the values:

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;

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

Then make the sequence the default for the new column

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

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

Finally, associate the sequence with the column (which is what a serial does in the background as well):

alter sequence tickets_ticket_id_seq owned by tickets.ticket_id;

---

如果表确实很大(数十"或几百")，那么创建新表可能会更快:

---

If the table is really big ("tens" or "hundreds" of millions) then creating a new table might be faster:

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;

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

Then re-create all foreign keys and indexes for that table.

这篇关于将自动增量列添加到按日期排序的现有表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！