How to import data from tsv file in postgreSQL procedure?

158 views Asked by At

I have a procedure:

CREATE OR REPLACE PROCEDURE import(IN tablename varchar, IN path text, IN separator char) AS $$
    BEGIN
            EXECUTE format('COPY %s FROM ''%s'' DELIMITER ''%s'' CSV HEADER;', tablename, path, separator);
    END;
$$ LANGUAGE plpgsql;

I call like this: CALL import('transactions', '/home/vladimir/Desktop/RetailAnalitycs_v1.0/datasets/Transactions.tsv','\t');

this option works on macOS but doesn't work on linux, what could be the reason? produces the following error: [58P01] ERROR: could not open file "/home/vladimir/Desktop/RetailAnalitycs_v1.0/datasets/Transactions.tsv" for reading: No such file or directory Подсказка: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

But I am 100% sure that the path to the file is correct.

I tried to use a different separator, rewrote the procedure, asked chatgpt.

0

There are 0 answers