Skip site navigation (1) Skip section navigation (2)

Re: COPY FROM with a serial not null column

From: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
To: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
Cc: PgSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: COPY FROM with a serial not null column
Date: 2002-08-16 16:27:07
Message-ID: 02081617270706.01454@splash.hq.jtresponse.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
On Friday 16 August 2002 17:19, Oliver Elphick wrote:
> On Fri, 2002-08-16 at 16:20, John Taylor wrote:
> > Hi,
> > 
> > I'm having trouble using COPY FROM with a table that has a serial NOT NULL column.
> > 
> > If I use psql to INSERT with a NULL value for the appropriate column, everything
> > works fine, and the next value in the sequence is inserted.
> > 
> > However, if I use COPY table FROM filename, I get an error:
> > ERROR:  copy: line 1, CopyFrom: Fail to add null value in not null attribute seq 
> > 
> > (The column in question is called seq).
> > 
> > Does anyone have ideas how I can get this to work ?
> 
> You can't use sequences in conjunction with COPY; you have to fill in
> the field in your input text file.
> 
> If it is a big file, you could use a tool such as awk or perl to write a
> value into each line of input.  After loading the file, you will need to
> set the sequence value:
> 
>    SELECT setval('sequence_name', last_value);
> 

OK,
I'll get the sequence value, and then create my copy file and insert the values manually.

Can I lock the sequence, to stop the numbers being re-used while I am creating and loading
the copyfile ?

Thanks
JohnT

In response to

pgsql-novice by date

Next:From: Chad ThompsonDate: 2002-08-16 16:52:53
Subject: Re: BLOBs
Previous:From: Oliver ElphickDate: 2002-08-16 16:19:11
Subject: Re: COPY FROM with a serial not null column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group