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?

Brainstorming

Answer 1

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

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

Fid

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. – Brainstorming

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. – Turpitude

Answer 2

I consider pg's sequences a hack and signs that incremental integers aren't the best way to key rows. Although pgsql didn't get native support for UUIDs until 8.3

http://www.postgresql.org/docs/8.3/interactive/datatype-uuid.html

The benefits of UUID is that the combination are nearly infinite, unlike a random number which will hit a collision one day.

Montagnard

"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. – Erbes

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

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. – Erbes

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