Question 268950 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Copy CSV File into Temporary Table

I have a temporary table with 4 columns

 CREATE TEMP TABLE tmp_import (
        a varchar(128), 
        b varchar, 
        c varchar(256), 
        d integer default null
        ) ON COMMIT DROP

I would like to copy data from a csv file into this temp table with command

COPY tmp_import FROM STDIN WITH ( DELIMITER ';', FORMAT 'csv', ENCODING 'UTF-8', HEADER 'true')

The CSV file header might have 3 or 4 columns like a;b;c;d or a;b;c, d column is optional. Like below;

a;b;c;d
31641212545;2021-01-01 11:00:00;http://www.google.nl;1
31641342545;2021-01-01 11:00:00;http://www.google.nl;1
31641310545;2021-01-01 11:00:00;http://www.google.nl;1
31641300545;2021-01-01 11:00:00;http://www.google.nl;1

or

a;b;c
31641212545;2021-01-01 11:00:00;http://www.google.nl
31641342545;2021-01-01 11:00:00;http://www.google.nl
31641310545;2021-01-01 11:00:00;http://www.google.nl
31641300545;2021-01-01 11:00:00;http://www.google.nl

The COPY command runs with 4 header CSV file but fails with 3 headers.

The exception thrown

org.postgresql.util.PSQLException: ERROR: missing data for column "d"
  Where: COPY tmp_import, line 2: "123456;2021-01-01 11:00:00;http://www.google.nl"

How can I fix this query or command to support both 3 and 4 headers and insert null for missing optional d header ?

Tags:

2

Answers


  1. 0

    You can’t do both with one command, but you can instruct it to handle the second case separately: demo

    copy (select '') to program $$
mkdir /tmp/myimports/ ;
echo 'a;b;c;d'   > /tmp/myimports/mycsv  ; 
echo 'a1;b1;c1;1'>>/tmp/myimports/mycsv  ;
echo 'a;b;c'     > /tmp/myimports/mycsv2 ;
echo 'a2;b2;c2'  >>/tmp/myimports/mycsv2
$$;

COPY tmp_import FROM '/tmp/myimports/mycsv' 
WITH ( DELIMITER ';', FORMAT 'csv', ENCODING 'UTF-8', HEADER 'true');
TABLE tmp_import;
    a b c d
    a1 b1 c1 1

    Note the explicit column name list after table name:

    COPY tmp_import(a,b,c) FROM '/tmp/mycsv2' 
WITH ( DELIMITER ';', FORMAT 'csv', ENCODING 'UTF-8', HEADER 'true');

TABLE tmp_import;
    a b c d
    a1 b1 c1 1
    a2 b2 c2 null

    You can switch between these by catching the exception:

    do $f$
  declare v_file text;
begin
  create temp table my_files(f text);
  copy my_files from program $$find /tmp/myimports/ -type f $$;
  for v_file in select f from my_files loop
    begin
    execute format($c$COPY tmp_import FROM %1$L
                      WITH ( DELIMITER ';', 
                             FORMAT 'csv', 
                             ENCODING 'UTF-8', 
                             HEADER 'true');
                   $c$, v_file);
    exception when others then
    execute format($c$COPY tmp_import(a,b,c) FROM %1$L
                      CSV DELIMITER ';' ENCODING 'UTF-8' HEADER;
                   $c$, v_file);
    end;
   end loop;
end $f$;
    Login or Signup to reply.
  2. 0

    If the CSV files has fewer columns than the table, you have to specify a list of columns, like

    COPY tmp_import (a, b, c) FROM STDIN;

    Since you probably don’t know ahead of time how the file will look, you could proceed as follows:

    • try to load the file with the COPY statement from the question

    • if there is no error, you are done

    • if there is an error, catch it and run a COPY statement with a column list

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search