Copy CSV with fewer columns than PostgreSQL table?

Question

Let's say I have a table like this following:

CREATE TABLE table_a 
(
  cola text,
  colb text,
  colc text,
  cold text,
  cole text
)

Currently, I am loading the table with the following:

\copy table_a from PATH/TO/CSV/CSV_FILE.csv DELIMITER ',' CSV HEADER;

where CSV_FILE.csv also has all 5 columns: cola, colb, colc, cold, cole.

But what if I have CSV_FILE_2.csv that only has cola, colb, colc?

I want to be able to do something like:

\copy table_a from PATH/TO/CSV/CSV_FILE_2.csv DELIMITER ',' CSV HEADER;

to insert new rows from CSV_FILE_2.csv but leaves cold and cole null.

But when I try to do the above, I get a

ERROR:  missing data for column "cold"

Is there an efficient way to use the copy command to just add the new rows from CSV_FILE_2.csv?

One workaround I thought of is insert the rows into a temporary table, insert the rows from the temporary table into table_a and then delete the temporary table, but that seems cumbersome.

IMSoP · Accepted Answer · 2020-07-30 21:31:06Z

2

The manual page for the \copy command only gives a brief overview of the syntax, and doesn't separate the "copy from" and "copy to" options, but does say this:

The syntax of this command is similar to that of the SQL COPY command. All options other than the data source/destination are as specified for COPY.

If we look up the manual page for COPY instead, we see a clearer synopsis of the COPY FROM version:

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] [ WHERE condition ]

Notably, this includes a column list, which is explained below:

For COPY FROM, each field in the file is inserted, in order, into the specified column. Table columns not specified in the COPY FROM column list will receive their default values.

This sounds like exactly what you're looking for (the default value for a nullable column being null, if not otherwise specified). So in your example, you would run:

\copy table_a (cola, colb, colc) from 'PATH/TO/CSV/CSV_FILE_2.csv' DELIMITER ',' CSV HEADER;

answered Jul 30, 2020 at 21:31

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

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

1 Comment

Vincent Over a year ago

Thanks, yes you're correct. This works for me by specifying the column names. I would have thought that by default if you don't specify any column names it would default to something functionality equivalent to specifying (cola, colb, colc) but it did not!

Collectives™ on Stack Overflow

Copy CSV with fewer columns than PostgreSQL table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related