Обсуждение: COPY FROM with a serial not null column

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 ?


Thanks
JohnT

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);

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And whatsoever ye shall ask in my name, that will I
      do, that the Father may be glorified in the Son."
                                          John 14:13

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