how to pass variable to copy command in Postgresql

Question

I tried to make a variable in SQL statement in Postgresql, but it did not work. There are many csv files stored under the path. I want to set path in Postgresql that can tell copy command where can find csv files.

SQL statement sample:

\set outpath '/home/clients/ats-dev/'
\COPY licenses (_id, name,number_seats ) FROM :outpath + 'licenses.csv' CSV HEADER DELIMITER ',';
\COPY uploaded_files (_id, added_date ) FROM :outpath + 'files.csv' CSV HEADER DELIMITER ',';

It did not work. I got error: no such files. The two files licneses.csv and files.csv are stored under /home/cilents/ats-dev on Ubuntu. I found some sultion that use "\set file 'license.csv'". It did not work for me becacuse I have many csv files. also I tried to use "from : outpath || 'licenses.csv'". it did not work ether. Appreciate for any helps.

Using 9.3.

Craig Ringer · Accepted Answer · 2017-07-28 03:30:22Z

9

It looks like psql does not support :variable substitution withinpsql backslash commands.

test=> \set somevar fred
test=> \copy z from :somevar
:somevar: No such file or directory

so you will need to do this via an external tool like the unix shell. e.g.

for f in *.sql; do
    psql -c "\\copy $(basename $f) FROM '$f'"
done

answered Jul 28, 2017 at 3:30

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

Anurag Paul · Accepted Answer · 2017-07-28 08:01:59Z

4

You can try COPY command

\set outpath '\'/home/clients/ats-dev/'
COPY licenses (_id, name,number_seats ) FROM :outpath/licenses.csv' WITH CSV HEADER DELIMITER ',';
COPY uploaded_files (_id, added_date ) FROM :outpath/files.csv' WITH CSV HEADER DELIMITER ',';

Note: Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. Similarly, the command specified with PROGRAM is executed directly by the server, not by the client application, must be executable by the PostgreSQL user. COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

Documentation: Postgresql 9.3 COPY

answered Jul 28, 2017 at 8:01

Anurag Paul

1478 bronze badges

2 Comments

user7070824 Over a year ago

Thanks for your help.@Anurag Paul. It did not work.

Anurag Paul Over a year ago

COPY requires the role to be superuser and the files and the postgresql to be on the same server, so it is a bit restrictive.

YetAnotherGuest · Accepted Answer · 2023-01-19 15:13:19Z

0

It may have been true when this was originally asked, that psql backslash commands didn't support variable interpolation, but in my PostgreSQL 14 instance that's no longer the case. However, the psql manpage is clear that \copy specifically does not support variable interpolation.

answered Jan 19, 2023 at 15:13

YetAnotherGuest

1

Comments

Renaud · Accepted Answer · 2025-08-15 12:44:46Z

0

\copy will not expand variables but you can use the\cd command and from there use a relative path in your \copy

answered Aug 15 at 12:44

Renaud

4,6998 gold badges46 silver badges74 bronze badges

Collectives™ on Stack Overflow

how to pass variable to copy command in Postgresql

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related