Conditionally copy data from csv file to PostgreSQL table

Question

Is there a way, using PL/pgSQL, to copy data from csv file to PostgreSQL table only if the file exists, something similar to this:

BEGIN
    IF file_exists(filename) THEN
        COPY table FROM 'filename' DELIMITER ',' CSV HEADER;
    END IF;
END;

You could use `pg_stat_file()` from here [File Operations](https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-GENFILE). It needs superuser privileges by default. — Adrian Klaver, Mar 25 '21 at 22:36

score 0 · Accepted Answer · answered Mar 25 '21 at 22:52

0

you can try something like this:

select exists(select * from pg_ls_dir('directory_name') as file where file  = 'fileName');

but the directory should exists. check this also

answered Mar 25 '21 at 22:52

Modar Na

Glad it helped I recommend you check [this source](https://meta.stackexchange.com/questions/88535/asking-for-someone-to-accept-your-answer) for inspiration – Modar Na Mar 25 '21 at 23:39
1

Thanks, that's exactly wahat I was looking for. For future lurkers, I decided to go with the simple for loop, so this way I will only copy the files that exist within certain directory to given tables: `FOR row IN select pg_ls_dir('/data') LOOP filename = row.pg_ls_dir; tablename = replace(filename, '.csv', ''); filename = '/data/' || filename; EXECUTE 'COPY ' || tablename || ' FROM ''' || filename || ''' DELIMITER '','' CSV HEADER'; END LOOP;` – MGrudi Mar 25 '21 at 23:41

1 Answers1