Добавить столбец автоинкремента в существующую таблицу, упорядоченный по дате

Question

У меня есть существующая таблица с именем "tickets" в базе данных со столбцами:

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

Мне нужно добавить новый столбец автоинкремента ticket_id, но генерируемые значения должны соответствовать значению столбца "date".

Я попробовал это:

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

Проблема в том, что он генерирует значения "ticket_id" в каком-то странном порядке, похоже, что он основан на столбце "id", который является первичным ключом таблицы.

Можно ли генерировать последовательные значения, отсортированные по "дате"? Это важно, поскольку "ticket_id" должен отображаться в соответствии с порядком, в котором были сгенерированы билеты.

Answer 1

Если вы добавите такой последовательный столбец, существующие строки будут автоматически обновлены в "произвольном" порядке.

Чтобы контролировать порядок, в котором генерируются идентификаторы, вам нужно сделать это в несколько шагов:

Сначала добавьте столбец без значения по умолчанию (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 = activities.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 activities;
alter table tickets add primary key (id);
alter sequence tickets_ticket_id_seq owned by tickets.ticket_id;

Затем заново создайте все внешние ключи и индексы для этой таблицы.