Re: Sequence Ids are not updating after COPY operation in PostgreSQL

On Wed, 2012-02-01 at 12:08 +0530, Siva Palanisamy wrote:
> Hi there!
>
> I'm inserting bulk records using COPY statement in PostgreSQL, and is successful. When I try to insert a record later manually, it throws duplicate sequence id error. What I realize is, the sequence ids are not getting updated in its cache. Should I manually update the sequence number to get the number of records after performing COPY? Isn't there a solution while performing COPY, just increment the sequence variable, that is, the primary key field of the table?
>
> For instance, if I insert 200 records, COPY does good and my table shows all the records. When I manually insert a record later, it says duplicate sequence id error. It very well implies that it didn't increment the sequence ids during COPYing as work fine during normal INSERTing. Instead of instructing the sequence id to set the max number of records, won't there be any mechanism to educate the COPY command to increment the sequence ids during its bulk COPYing option?
>
> Please clarify me on this. Thanks in advance!
>

You usually need to use DEFAULT for the column, so that the sequence is
auto-incremented.

If you don't use DEFAULT, you need to set yourself the value of the
sequence.

PS: this isn't specific to COPY. INSERT behaves the same.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org