Postgres How to import CSV ignoring a column id is bigserial-CodePudding

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

CodePudding user response：

You can specify a column list with COPY:

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

Here you simply list all columns except id.

CodePudding user response：

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.