Postgresql 将CSV文件复制到临时表

您不能用一条命令同时处理这两种情况，但可以指示它分别处理第二种情况: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;

请注意表名后面的显式列名列表:

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

TABLE tmp_import;

您可以通过捕获异常在这两个选项之间进行切换:

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$;