The assorted finds of Artefact Publishing
Would you, having read the documentation about sequence functions in the PostgreSQL, think that a call to nextval would return 1 if there were already rows in the database added via COPY table FROM stdin? Would you not expect that nextval would return, say, 3, if there were two such rows in the table using that sequence?
Before I move on from this, does anyone have an explanation for this unintuitive behaviour? I like things to make a certain amount of sense, you see.
Posted by jamie on March 4, 2004 13:08+13:00
So the issue is that when using COPY table FROM stdin and specifying a number for the primary key (which is normally taken from the sequence, as when INSERTing without giving a value for the primary key column), the sequence is not consulted or updated at all. Which is, I guess, fair enough given your explanation.
So, all I need to do is to set the value of each sequence after I’ve copied the data into the table which uses that sequence.
Thank you.
Posted by: Jamie on March 7, 2004 10:03+13:00
The sequence is just a function to give you a bunch of unique, increasing numbers. The fact that you happen to be using them for primary keys is irrelevant. Postgres does not know or care what you do with these numbers.
Qs for you: how would Postgres "know" that you had already used some numbers in a sequence? How would it associate those numbers with a column in a particular table? What if you (bizarrely, I know) used the same sequence for keys in different tables?
Posted by: stephen on March 6, 2004 22:02+13:00