CODEWITHSUNDEEP
postgresqlcsv
George L
George L

Reputation: 1738

Autogenerating ID column when doing COPY from CSV

I have a simple table (4 text columns, and an ID column). I am trying to import my CSV file which has no ID column.

In Postico I have the schema setup as such: enter image description here

DROP TABLE changes;
CREATE TABLE changes(
id    SERIAL PRIMARY KEY,
commit_id TEXT,
additions INTEGER,
deletions INTEGER,
file_id TEXT
);
CREATE TEMP TABLE tmp_x AS SELECT * FROM changes LIMIT 0;
COPY tmp_x(commit_id,additions,deletions,file_id) FROM '/Users/George/git-parser/change_file' (format csv, delimiter E'\t');
INSERT INTO changes SELECT * FROM tmp_x
ON CONFLICT DO NOTHING;
DROP TABLE tmp_x;

But I am getting the error ERROR: null value in column "id" violates not-null constraint

Upvotes: 3

Views: 3397

Answers (1)

user330315
user330315

Reputation:

You need to specify the columns:

COPY tmp_x (commit_id, additions, deletions, file_id) 
FROM '/Users/George/git-parser/change_file' (format csv, delimiter E'\t');

The order of columns specified in the copy statement must obviously match the order of the columns in the input file.

You need to change your insert statement as well. 

INSERT INTO changes SELECT * FROM tmp_x

will insert all columns from tmp_x into the target table, but as you did not define the id column as serial in the tmp_x table, nothing got generated and null values were inserted. And your insert statement just copies those null values.

You need to skip the id column in the insert statement:

INSERT INTO changes (commit_id,additions,deletions,file_id) 
SELECT commit_id,additions,deletions,file_id
FROM tmp_x
ON CONFLICT DO NOTHING;

You can actually remove the id column from tmp_x

Upvotes: 3

Related Questions