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?

Grey Panther · Accepted Answer · 2008-09-29 09:09:07Z

38

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

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

edited Sep 29, 2008 at 9:09

answered Sep 29, 2008 at 9:01

Grey Panther

13.2k7 gold badges50 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Hilton Over a year ago

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 Over a year ago

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.

TravisO · Accepted Answer · 2008-11-06 18:13:13Z

-1

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.

answered Nov 6, 2008 at 18:13

TravisO

9,5634 gold badges39 silver badges45 bronze badges

4 Comments

wberry Over a year ago

"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 Over a year ago

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

wberry Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related