How to copy a csv file using SQL

Question

I need to import a csv in my database. I created a function in sql that does the import:

 CREATE OR REPLACE FUNCTION import_match_csv(filename varchar(500), username varchar(500)) RETURNS VOID AS $$
   BEGIN
    CREATE TABLE match_import (....));

    COPY match_import FROM 'filename' DELIMITER ',' CSV HEADER;

    INSERT ....

    DROP TABLE match_import;
   END;
$$ LANGUAGE plpgsql;

Calling the function writing in the psql prompt

 SELECT * FROM import_match_csv('/Users/benny/Desktop/match.csv', 'benny');

I get the error:

Error: ERROR: could not open file "filename" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. CONTEXT: SQL statement "COPY match_import FROM 'filename' DELIMITER ',' CSV HEADER" PL/pgSQL function import_match_csv(character varying,character varying) line 33 at SQL statement

Drop the single quotes around filename in your COPY statement, it's just a string literal at the moment. — OTTA
@OTTA If I drop the single quotes I get a sintax error at filename — bennius

Laurenz Albe Laurenz Albe · Accepted Answer · 2019-09-03T15:03:33

You'll have to use dynamic SQL, since COPY doesn't support parameters:

EXECUTE format(
           'COPY match_import FROM %L (FORMAT ''csv'', DELIMITER '','', HEADER)',
           filename
        );

How to copy a csv file using SQL

1 Answers