How to specify list of values for a postgresql sequence

Question

I have a list of integer values X and I want a primary key for my table Y to come from that list of integer values. I was wondering if this could be done using sequences. In other words: is there a way to tell a PostgreSQL sequence to use this list X to generate primary keys for my table Y?

One way of doing this would be to use a sequence that contains the index of the last used integer from the list X, call setval() on the sequence, get the next value and try to insert it into my table Y. In case of concurrent requests there will be an error, in which case I need to try with the next value from the list X. I would like to know what other (better) ways there are to achieve what I intend to do.

Disagree

Are these values for rows you'd like to prepopulate into your database or do you just want to use the provided set for new rows? – Dyedinthewool

I want to use the provided set for new rows. – Disagree

Answer 1

Could work like this:

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.priv_id(seq_id int primary key, id int);

INSERT INTO x.priv_id
SELECT generate_series(1,100,1), (random() * 1000)::int;

CREATE SEQUENCE x.priv_seq;

SELECT id
FROM   x.priv_id
WHERE  seq_id = (SELECT nextval('x.priv_seq'));

Major points:

1) Create a lookup table with two numbers
- seq_id is counting from 1 and your primary key.
- id is your numbers in sequence (I substituted random numbers here).
2) Create a helper sequence.
3) Get your numbers with a SELECT like above.
You need the subselect, or all values will be returned at once.

This solution gives all the security nextval() has to offer for concurrency.
Create a unique index on priv_id(id) if you want to make sure your custom id's are unique.

Choreodrama

Answer 2

generate_series can be used to produce a list of all numbers every generated by a sequence:

select * 
from generate_series(1, (select last_value from my_sequence), 1) as x

Note: This assumes the sequence (here my_sequence) started at 1 and incremented by 1. To change these assumptions, change the parameters appropriately.

Shorthorn

thanks a lot for your response @Bohemian, however the problem that i have is that list of values which i want to use as primary keys doesn't come from a sequence. I have updated the question to make it easier to understand – Disagree

Answer 3

The most general way to provide your own set of valid numbers is to store them in a table, and set a foreign key reference to it.

That solves the problem of using your own list, but it doesn't solve the problem of fetching the next number from the list. To do that, you'd need to write your own function. (You can't compel a PostgreSQL sequence to operate on your table instead of using its internals.) It's not particularly hard to do, though.

Another alternative that's often overlooked--perhaps with good reason--is writing your own sequence. PostgreSQL is open source; you can write your own sequencer based on existing sequence code. Compile it, test it, and you're off to the races. (I don't recommend doing that, though.)

Goldberg