Add auto increment column to existing table ordered by date

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_idfrom (   select id, nextval('tickets_ticket_id_seq') as new_id   from tickets   order by "date") twhere 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_newasselect id, nextval('activities_ticket_id_seq') ticket_id, "date", statusfrom ticketsorder 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.