auto fill serial id field with default value in copy operation

From: "karsten vennemann" <karsten(at)terragis(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: auto fill serial id field with default value in copy operation
Date: 2010-10-22 21:03:47
Message-ID: E417D23A22184DDCA030E7628ADC9EB9@snuggie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
<http://www.terragis.net> www.terragis.net

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-10-22 21:31:11 Re: auto fill serial id field with default value in copy operation
Previous Message Andre Lopes 2010-10-22 20:29:00 Tools for form generation in PHP/HTML from database models/queries