Peter's answer is probably the way to go, but wanted to chime in with another approach using COPY FROM PROGRAM
. Unfortunately I only have a linux env so I can't give you a command/statement that will work for your case, but hopefully this gives you the idea and/or is useful for someone else.
COPY FROM
with a file can only support one file at a time, but being a bit clever with a COPY FROM PROGRAM
is one way of importing multiple files with one COPY
. Here's a trivial example:
sh-5.0$ mkdir /tmp/so_267604/
sh-5.0$ cd /tmp/so_267604/
sh-5.0$ echo -e "cola,colb\naval1,bval1\naval2,bval2" > first.csv
sh-5.0$ cat first.csv
cola,colb
aval1,bval1
aval2,bval2
sh-5.0$ echo -e "cola,colb\naval3,bval3\naval4,bval4" > second.csv
sh-5.0$ tail --quiet -n +2 *.csv
aval1,bval1
aval2,bval2
aval3,bval3
aval4,bval4
sh-5.0$ psql -X testdb
testdb=# create table tt(cola text, colb text);
CREATE TABLE
testdb=# copy tt from program 'tail --quiet -n +2 /tmp/so_267604/*.csv' csv;
COPY 4
testdb=# select * from tt;
cola | colb
-------+-------
aval1 | bval1
aval2 | bval2
aval3 | bval3
aval4 | bval4
(4 rows)
Note that there's no HEADER
modifier on the copy statement; this because -n +2
on the tail
command will always start at line #2 of each file it outputs. This should not break anything; the HEADER directive only ever tells pg to skip reading one line, the actual data is always imported using column order specified in the COPY statement, or the table's column order if the COPY did not specify it.