CODEWITHSUNDEEP
postgresqlpsql
Hari
Hari

Reputation: 333

postgresql copy from csv file into table in windows through procedure

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 ?

Upvotes: 0

Views: 1833

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247260

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.

Upvotes: 2

Related Questions