up vote 10 down vote favorite
1

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?

share
up vote 24 down vote accepted

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

update person set unique_number = nextval('number_sequence');
share
    
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. – Peter Hilton Sep 29 '08 at 9:11
    
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. – cms Jul 26 '12 at 9:13
up vote 0 down vote

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.

share
    
"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. – wberry Jan 28 '14 at 15:48
1  
I think you under estimate how big 2^128 is, go read up about uuid on Wikipedia. – TravisO Jan 30 '14 at 14:41
    
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. – wberry Jan 31 '14 at 6:11
    
Postgres has its own uuid generator - which I haven't seen - but it may well be better than Python's, fwiw. – Ghoti Mar 9 at 16:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.