Postgres Pro
Downloads
Home   >   mailing lists

Thread: auto fill serial id field with default value in copy operation

auto fill serial id field with default value in copy operation

From
"karsten vennemann"
Date:

I'm trying to load data from a csv file via copy command into a table with the first column record_id specified NOT NULL  (this is a serial field defined as follows:
 
ALTER TABLE records ADD COLUMN record_id integer;
ALTER TABLE records ALTER COLUMN record_id SET STORAGE PLAIN;
ALTER TABLE records ALTER COLUMN record_id SET NOT NULL;
ALTER TABLE records ALTER COLUMN record_id SET DEFAULT nextval('records_record_id_seq'::regclass);

Since my inport data in the csv files have no value for the first field and look like this ,1015,1,0,0,0,0....
I get this ERROR: null value in column "record_id" violates not-null constraint
What is the best way to load my data via copy into the db and get the record_id field auto filled (with the next id value in squence) ?
 
I'm trying this using the following command:
copy records from '/var/data/import1.csv' using delimiters ','  with null as '';

Karsten Vennemann
Terra GIS LTD
Seattle, WA  98112
USA 
www.terragis.net

Re: auto fill serial id field with default value in copy operation

From
Tom Lane
Date:
"karsten vennemann" <karsten@terragis.net> writes:
> What is the best way to load my data via copy into the db and get the record_id field auto filled (with the next id
valuein squence) ? 

You have to omit the column from the copy data altogether, and then
list just the columns that are supplied in the data in the COPY command's
column list.

            regards, tom lane

Re: auto fill serial id field with default value in copy operation

From
"karsten vennemann"
Date:
Yes it worked when I ommited the serial field in the copy command (and in
the import source file). As a reference for others:

COPY records (quad_id, species_id, observation_value) from
'/var/www/data/data_import2.csv' using delimiters ','  with null as '';

Karsten



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, October 22, 2010 14:31
> To: karsten vennemann
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] auto fill serial id field with default
> value in copy operation
>
> "karsten vennemann" <karsten@terragis.net> writes:
> > What is the best way to load my data via copy into the db
> and get the record_id field auto filled (with the next id
> value in squence) ?
>
> You have to omit the column from the copy data altogether,
> and then list just the columns that are supplied in the data
> in the COPY command's column list.
>
>             regards, tom lane