Copy multiple CSV files into postgres

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

I tried the answer above but I got an error when working with more then one file. I think on the second file it didn't cut off the header.

This worked fot me:

# get filenames
IMPFILES=(path/FileNamepart.csv)

# import the files
for i in ${IMPFILES[@]}
    do
        psql -U user -d database -c "\copy TABLE_NAME from '$i' DELIMITER ';' CSV HEADER"
        # move the imported file
        mv $i /FilePath
    done

In my case I move every file afer it is imported. If an error occours I know where to look. And I can run the script again if there are new files put in that location.

If you want to use the PROGRAM (Postgres > 9.3) keyword but you have the header in each csv file you can use awk :

COPY product(title, department) FROM PROGRAM 'awk FNR-1 ys*.csv | cat' DELIMITER ',' CSV;

Starting with Postgres 9.3, you can run a shell command using the PROGRAM keyword within the COPY command.

COPY product(title, department) from PROGRAM 'cat ys*.csv' FORMAT CSV HEADER

You can loop through the filenames using pg_ls_dir.

DO $$

DECLARE file_path TEXT; -- Path where your CSV files are
DECLARE fn_i TEXT; -- Variable to hold name of current CSV file being inserted
DECLARE mytable TEXT; -- Variable to hold name of table to insert data into

BEGIN

    file_path := 'C:/Program Files/PostgreSQL/9.6/data/my_csvs/'; -- Declare the path to your CSV files. You probably need to put this in your PostgreSQL file path to avoid permission issues.
    mytable := 'product(title,department)'; -- Declare table to insert data into. You can give columns too since it's just going into an execute statement.

    CREATE TEMP TABLE files AS 
    SELECT file_path || pg_ls_dir AS fn -- get all of the files in the directory, prepending with file path
    FROM pg_ls_dir(file_path);

    LOOP    
        fn_i := (select fn from files limit 1); -- Pick the first file
        raise notice 'fn: %', fn_i;
        EXECUTE 'COPY ' || mytable || ' from ''' || fn_i || ''' with csv header';
        DELETE FROM files WHERE fn = fn_i; -- Delete the file just inserted from the queue
        EXIT  WHEN (SELECT COUNT(*) FROM files) = 0;
     END LOOP;

END $$;

Just one option more, using pg_ls_dir and format(). Inserting all files from 'E:\Online_Monitoring\Processed\' folder into ONLMON_T_Online_Monitoring table.

DO $$
DECLARE
  directory_path VARCHAR(500);
    rec RECORD;
BEGIN
  directory_path := 'E:\\Online_Monitoring\\Processed\\';
    FOR rec IN SELECT pg_ls_dir(directory_path) AS file_name
    LOOP
      EXECUTE format(
            '
                COPY ONLMON_T_Online_Monitoring
                    (
                        item
                    , storeCode
                    , data
                    )
                FROM %L
                WITH (FORMAT CSV, HEADER);
                ', directory_path || rec.file_name
        );
    END LOOP;
END; $$;

For a process that works with a remote server or a local one, you can use:

export PGPASSWORD=12345;
for x in $(ls *.csv)
do
    psql -d <database> -U <username> -c "\copy <schema>.<tablename> FROM '$x' CSV HEADER"
done

It's subtle, but this is different from the commands above since it users the built in \copy command which can ship files over a server and doesn't need them available there locally.