CODEWITHSUNDEEP
sqlpostgresqlinsertsql-update
iskandarblue
iskandarblue

Reputation: 7526

Populate column with generated series

Having accidentally deleted information from an auto-incrementing primary key id column in Postgres, my goal is to re-populate it with a generated series of data. For example:

UPDATE mytable
SET id = (SELECT GENERATE_SERIES(1,3456))

This throws the error: ERROR: more than one row returned by a subquery used as an expression

UPDATE mytable
SET id = SELECT a.n from generate_series(1, 3456) as a(n)

(throws a syntax error)

What is the correct method to update a single column with a generated series in PostgreSQL? Why does SET not work here?

Upvotes: 0

Views: 229

Answers (2)

melcher
melcher

Reputation: 1601

If you deleted the id column of some rows, want to give them an ID again and don't care what the ID is you could use something like:

UPDATE mytable 
SET id = nextval('mytable_id_seq')
WHERE id IS NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you have a primary key, you can do:

UPDATE mytable t
    SET id = new_id
    FROM (SELECT tt.*, ROW_NUMBER() OVER (ORDER BY id) as new_id
          FROM mytable tt
         ) tt
    WHERE t.id = tt.id;

Upvotes: 3

Related Questions