CODEWITHSUNDEEP
postgresql
Ben
Ben

Reputation: 675

Add a serial column based on a sorted column

I have a table that has one column with unordered value. I want to order this column descending and add a column to record its order. My SQL code is:

select *
into newtable
from oldtable
order by column_name desc;

alter table newtable add column id serial;

Would this implement my goal? I know that rows in PostgreSQL have no fixed order. So I am not sure about this.

Upvotes: 2

Views: 2261

Answers (2)

arigo
arigo

Reputation: 31

You can first create a new table, sorted based on the column you want to use:

CREATE TABLE newtable AS
SELECT * FROM oldtable
ORDER BY column_name desc;

Afterwards, since you want to order from the largest to the smallest, you can add a new column to your table:

ALTER TABLE newtable ADD COLUMN id serial unique;

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324521

Rather than (ab)using a SERIAL via ALTER TABLE, generate it at insert-time.

CREATE TABLE newtable (id serial unique not null, LIKE oldtable INCLUDING ALL);

INSERT INTO newtable 
SELECT nextval('newtable_id_seq'), *
FROM oldtable
ORDER BY column_name desc;

This avoids a table rewrite, and unlike your prior approach, is guaranteed to produce the correct ordering.

(If you want it to be the PK, and the prior table had no PK, change unique not null to primary key. If the prior table had a PK you'll need to use a LIKE variant that excludes constraints).

Upvotes: 5

Related Questions