更新时间:2023-09-24 16:48:10
如果添加这样的串行列,则现有行将自动以任意"形式更新.订单.
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.