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, Commented Sep 3, 2019 at 14:57
@OTTA If I drop the single quotes I get a sintax error at filename — bennius
– bennius, Commented Sep 3, 2019 at 15:01

Laurenz Albe · Accepted Answer · 2019-09-03 15:03:33Z

1

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
        );

answered Sep 3, 2019 at 15:03

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to copy a csv file using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related