16

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?

Peter Hilton
  • 17,211
  • 6
  • 50
  • 75

2 Answers2

37

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

update person set unique_number = nextval('number_sequence');
Grey Panther
  • 12,870
  • 6
  • 46
  • 64
  • 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 09:11
  • 1
    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 09:13
0

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.

TravisO
  • 9,406
  • 4
  • 36
  • 44
  • "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
  • 2
    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 06:11
  • Postgres has its own uuid generator - which I haven't seen - but it may well be better than Python's, fwiw. – Ghoti Mar 09 '17 at 16:44