Postgresql copy command not finding file

Question

when running:

~/fidelity/releases/20220907033831$ ls -a
.
..
.browserslistrc  
221005_users_all.csv
_private

the presence of a file is confirmed.

However, when launching a postgresql command

psql fidelity_development
COPY users (id,migrated_id,[...]) FROM '~/fidelity/releases/20220907033831/221005_users_all.csv'  DELIMITER ';' CSV HEADER;

The response is unexpected: ERROR: could not open file "~/fidelity/releases/20220907033831/221005_users_all.csv" for reading: No such file or directory

What am I missing to determine why postgresql cannot see this file?

note this directory was also simlinked as fidelity/current and the same result was obtained when referring to that directory for the file, whereas bash sees it.

Try to use \copy as copy is server based and \copy is client based. — michal.jakubeczy
– michal.jakubeczy, Commented Oct 12, 2022 at 8:49
that worked. however, I clearly do not grasp the subtlety of how being on the server that hosts the database and connecting to it, I would find myself in a 'client-based' contest. — Jerome
– Jerome, Commented Oct 12, 2022 at 8:53
in case DB server is on the same machine COPY command handles the path from the scope of a location of a DB server - that could cause the problem with finding the file. You used tilde meaning the current directory of a user. DB server might run under a different user account. — michal.jakubeczy
– michal.jakubeczy, Commented Oct 12, 2022 at 8:56

michal.jakubeczy · Accepted Answer · 2022-10-12 08:54:43Z

1

Use \COPY command as this one is client based and handles the local path correctly.

While COPY is server based and this could cause issues finding your file.

answered Oct 12, 2022 at 8:54

michal.jakubeczy

9,7671 gold badge71 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

Postgresql copy command not finding file

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