Postgresql - pass parameters to COPY in an sql script

Question

I can use the -v v1=foo syntax just fine for queries in my sql script, but I can't figure out how to use that parameter in a copy statement. I'd like to execute the script like:

psql -d my_db -f ./exports.sql -v v1="'/Users/username/test.json'"

And in the script do some version of:

copy (
 select * from bar     
) to :v1;

or

DO $$ 
BEGIN
  EXECUTE
   'copy (select * from bar) to ' || :v1;
END $$

or

DO $$ 
BEGIN
  EXECUTE
   format('copy (select * from bar) to %L',:v1);
END $$

But none of the above work :(

Laurenz Albe · Accepted Answer · 2021-06-29 16:09:53Z

2

Variable substitution doesn't work in a string literal.

Use psql's \gexec:

SELECT format(
          $$copy (select * from bar) to %L$$,
          :v1
       ) \gexec

edited Jun 29, 2021 at 16:09

answered Jun 29, 2021 at 15:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

6 Comments

chrismarx Over a year ago

THANK YOU! That definitely works. Finally I have a way to not hardcode the export paths for copy

chrismarx Over a year ago

I'm getting stuck again when I try to use %L value concatenated with a string, what's the proper way to do that? As in COPY ... FROM %L||'myfile.csv' ?

Laurenz Albe Over a year ago

Sounds like a new question.

chrismarx Over a year ago

It's still passing parameters to copy in a script executed from psql, or am I missing something larger than that?

Laurenz Albe Over a year ago

Comments are not for follow-up questions. Please ask a new one.

|

Collectives™ on Stack Overflow

Postgresql - pass parameters to COPY in an sql script

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related