I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable.

COPY (SELECT cname.portal from user) To '/tmp/out.csv' With CSV;

Where cname is my variable inside anonymous block. I try to use EXECUTE format but it also didn't work. I tried something like:

EXECUTE format ('    COPY (select * from %s.portal,cname ) To '/tmp/out1.csv' With CSV');

In plpgsql code (not in plain SQL!):

EXECUTE format($$COPY (select * from %I.portal) TO '/tmp/out1.csv' WITH CSV$$, cname);

cname being the variable holding a (yet unescaped) schema name.

You need to understand:

If you are processing this with a client where $ has a special meaning, you need to escape it properly. As a quick fix, you can switch to:

EXECUTE format('COPY (select * from %I.portal) TO ''/tmp/out1.csv'' WITH CSV', cname);
  • for it I am still getting syntax error. EXECUTE format(5222COPY (select * from %I.... – user3526905 Feb 26 '15 at 5:26
  • @user3526905: Seems to be an issue with the $ character, which seems to be lost in translation. I added an alt. – Erwin Brandstetter Feb 26 '15 at 5:33
  • Erwin I am not getting any error but it is unable to write to csv file.I am not getting any contents even though there are contents in table. – user3526905 Feb 26 '15 at 6:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.