Postgres How to import CSV ignoring a column id is bigserial

Question

I have a CSV which contains column names and data, but does not have the column ID. When I import my data via this command line:

type "..\MyCSVtoImport.csv" | "..\psql.exe" -h MyServerPostgres -p 5432 -U "MyUser" -c " COPY mytable FROM STDIN DELIMITER ',' CSV HEADER; " MyBase

I still have this error : ERROR: missing data for column "id"

Thanks for your help

Are you appending to the table or creating new? – Adrian Klaver Oct 11 '21 at 16:01 — Adrian Klaver, Oct 11 '21 at 16:01

score 1 · Answer 1 · answered Oct 11 '21 at 11:21

1

You can specify a column list with COPY:

COPY mytable (col1, col2, ...) FROM STDIN;

Here you simply list all columns except id.

answered Oct 11 '21 at 11:21

Laurenz Albe

209,280
17
206
263

thk, I have over 200 columns, is there a way to say except id – SebP Oct 11 '21 at 11:44
No, unfortunately not. You could query `information_schema.columns` and construct the SQL statement programmatically. – Laurenz Albe Oct 11 '21 at 11:47

score 0 · Answer 2 · answered Oct 11 '21 at 16:49

Alternate solution:

CREATE mytable_import AS SELECT * FROM mytable LIMIT 0;
ALTER TABLE mytable_import DROP COLUMN id;

--COPY to mytable_import

INSERT INTO mytable SELECT nextval('mytable_id_seq') AS id, * FROM mytable_import;

Substitute actual sequence name into nextval. Also this may need tweaking depending on whether you are appending or starting with empty mytable. In appending an ON CONFLICT clause maybe needed.

Postgres How to import CSV ignoring a column id is bigserial

2 Answers2