Add auto increment column to existing table ordered by date

Question

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)

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

I tried this:

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

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.

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.

Answer 1

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

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

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;

Then update the existing rows

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');

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.