How to update unique values in SQL using a PostgreSQL sequence?

Question

In SQL, how do update a table, setting a column to a different value for each row?

I want to update some rows in a PostgreSQL database, setting one column to a number from a sequence, where that column has a unique constraint. I hoped that I could just use:

update person set unique_number = (select nextval('number_sequence') );

but it seems that nextval is only called once, so the update uses the same number for every row, and I get a 'duplicate key violates unique constraint' error. What should I do instead?

Solution

Don't use a subselect, rather use the nextval function directly, like this:

update person set unique_number = nextval('number_sequence');

Comments

[ngeek]

Thanks - that works. I got caught by the sub-select because I was trying to use the same sequence number for two columns, but I don't really need to do that.

[cms]

If you want to re-use the same sequence value after you have called nextval('sequence') , you can use the related function currval('sequence'), which returns the current sequence.

[wberry]

"Nearly infinite?" The docs say it's a 128-bit integer type. That's a lot, but it isn't infinite, and it is nearly certain to collide before all 2^128 values are used. Additionally, ORMs will likely have to convert to/from string types to use this. Not a clear win in my view.

[TravisO]

I think you under estimate how big 2^128 is, go read up about uuid on Wikipedia.

[wberry]

It's mostly academic, but a Postgres sequence can be more collision-proof than a UUID depending on how it is created. Python's uuid module uses a random component, but substantially less than 128 random bits. Sequences only collide if they cycle, random numbers collide ... randomly.

[Ghoti]

Postgres has its own uuid generator - which I haven't seen - but it may well be better than Python's, fwiw.