CODEWITHSUNDEEP
postgresqlplpgsql
George D.
George D.

Reputation: 265

PostgresQL: passing a partial file path as a command line parameter

I'm writing a script to export a subset of data from a PostgreSQL instance, and I'm running into a bit of trouble trying to programmatically construct an output file path.

What I would like to be able to do is something like:

psql -d mydb -f my_script.sql -v out_path="'/path/to/my/output/dir/'"

Which I have attempted with the following simple example

COPY my_table TO :out_path || 'file.csv' WITH CSV;

However this results in the following error:

psql:my_script.sql:4: ERROR:  syntax error at or near "||"
LINE 1: ...path/to/my/output/dir/' || 'my_tabl...

Is there a better way to piece together the output path I want in the script? Am I going about this all wrong? Any guidance would be much appreciated!

Upvotes: 0

Views: 537

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

In this case you should not to use operator ||. It is SQL operator, and COPY doesn't allow expression there.

psql variables are concatenated by default. So in this case || operator is useless. 

\set out_path /path/to/my/output/dir/
\echo :out_path/file_csv

postgres=# \set out_path /path/to/my/output/dir/
postgres=# \echo :out_path/file_csv
/path/to/my/output/dir//file_csv

If you don't run this script under super user, then you cannot to write to server file system. Is not good do these operation under super user. So probably you would to use \COPY commands, that is joined with client side files system.

So your script can looks like:

-- does concat too
\set file_path :out_path 'file.csv' 
\copy my_table to :file_path csv

Upvotes: 1

Related Questions