Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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 1


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

CREATE TABLE


begin transaction;

COPY tmp_import FROM '/tmp/myimports/mycsv' 
WITH ( DELIMITER ';', FORMAT 'csv', ENCODING 'UTF-8', HEADER 'true');
TABLE tmp_import;

rollback;

BEGIN



COPY 1




a
b
c
d


a1
b1
c1
1



SELECT 1



ROLLBACK


begin transaction;

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

rollback;

BEGIN



COPY 1




a
b
c
d


a2
b2
c2
null



SELECT 1



ROLLBACK


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

TABLE tmp_import;

DO




a
b
c
d


a1
b1
c1
1

a2
b2
c2
null



SELECT 2

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