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