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.