Copy multiple CSV files into postgres

Question

I am writing a SQL script to copy multiple .CSV files into a postgres database like this:

COPY product(title, department) from 'ys.csv' CSV HEADER;

I have multiple files I want to copy in. I don't want:

COPY product(title, department) from 'ys1.csv' CSV HEADER;
COPY product(title, department) from 'ys2.csv' CSV HEADER;
COPY product(title, department) from 'ys3.csv' CSV HEADER;
COPY product(title, department) from 'ys4.csv' CSV HEADER;
COPY product(title, department) from 'ys5.csv' CSV HEADER;

I would like to use a for loop for this instead of multiple copy commands. Is this possible? Thanks

Answer 1

In linux pipe the output of the files listing to psql. Make copy use the standard input:

cat /path_to/ys*.csv | psql -c 'COPY product(title, department) from stdin CSV HEADER'

Look for the equivalent in other OSs