postgresql copy from csv file into table in windows through procedure

Question

below is my procedure executed to upload file into table and do joins etc.

CREATE OR REPLACE FUNCTION sp_product_price()
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    AS $BODY$
BEGIN
    truncate table prd_product_data;
    truncate table price_import;    
    COPY price_import FROM 'C:\Users\Ram\Documents\prices.csv'  CSV HEADER; 
    truncate table active_product_price;    
    insert into active_product_price(key,name,price)
    SELECT prd.key,prd.name,prd.price FROM prd_product_data prd JOIN price_import import ON prd.name = import.name;  
    raise notice 'success';

END
$BODY$;

Above procedure giving error could not open file "C:\Users\Ram\Documents\prices.csv" 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.

I have given access to file for everyone in file properties.

I tried \copy in procedure but this gives error syntax error at or near "\" .

\copy is working when I ran cmd in psql but not in above procedure.

Is there a way to import file into table in above procedure/functions ?

You may have given the permissions to the file, but did you do the same for the directories in the path? — Adrian Klaver
– Adrian Klaver, Commented Sep 13, 2021 at 20:01
The file needs to be on the C: of the database server computer, not the C: of the client computer (if those are different). — jjanes
– jjanes, Commented Sep 13, 2021 at 21:14

Laurenz Albe · Accepted Answer · 2021-09-14 01:38:32Z

2

The procedure and the COPY statement are running on the database server, so the file C:\Users\Ram\Documents\prices.csv must be on the database server as well (and your database user must either be a superuser or a member of pg_read_server_files).

The only way you can use COPY to load data from the client is COPY ... FROM STDIN, and you won't be able to use that in a procedure.

\copy is a psql command, not an SQL statement, so it cannot be used in a procedure either.

My suggestion is to use \copy, but to forget about the procedure.

answered Sep 14, 2021 at 1:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

postgresql copy from csv file into table in windows through procedure

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