Postgresql: is it possible to COPY only specified columns without inserting null into other columns

Question

Use Case:

I've copied specified columns from table to csv file using COPY TO command. CSV looks like this:

id,name
1,For data test

Later I've tried to import data from this csv file to table using COPY FROM command and I've received an error, because csv file contains not the entire table, but specific columns only. Error is:

null value in column "[column_name]" violates not-null constraint
DETAIL:  Failing row contains (1, null, For data test, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null).
CONTEXT:  COPY [table_name], line 2: "1,For data test"

Question:

Is it possible to insert only specified columns from file without touching other columns, i.e. leaving other columns to their current values? I haven't found any information about this in Postgres documentation.

user330315 · Accepted Answer · 2020-08-28 06:22:27Z

2

You are apparently looking for an UPDATE statement, but copy will always run INSERTs.

You will need to import the file into a staging (temporary) table, and then run an UPDATE from there.

Something along the lines:

create table data_import
(
  id integer, 
  name text
);

copy data_import from 'your_file.csv';

Once that is finished, you can update your real table from that:

update the_table t
  set name = i.name
from data_import i
where i.id = t.id;

This assumes that id is the primary key (or at least unique) in the target table.

answered Aug 28, 2020 at 6:22

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Mikayel Over a year ago

Yeah, looks like this is the best solution for my case. Thanks!

Collectives™ on Stack Overflow

Postgresql: is it possible to COPY only specified columns without inserting null into other columns

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related