Export data in file in Postgres

Question

I have one table with id, name and complex queries. Below is just a sample of that table..

ID name Query 1 advisor_1 "Select * from advisor" 2 student_1 "Select * from student where id = 12" 3 faculty_4 "Select * from student where id = 12"

I want to iterate over this table and save each record into the csv file

Is there any way I can do it though Anonymous block automatically. I don't want to do this manually as table has lots of rows.

Can anyone please help?

COPY TO with csv in dynamic SQL would work in a DO block but you need to be superuser for the permission to write into server-side files. — Daniel Vérité
– Daniel Vérité, Commented Dec 1, 2017 at 19:05
@DanielVérité I tried that .. but I dont have super user permission.. Is there any other way? — Madhu
– Madhu, Commented Dec 1, 2017 at 19:36
Without superuser you can only copy to standard output, but you can't do it from a anonymous block. However it's relatively easy to do in any programming language, just iterate over results of your table and then issuing COPY for each one. Take a look on this example using java. — Michel Milezzi
– Michel Milezzi, Commented Dec 1, 2017 at 19:59
Can I use \copy command in anonymous block? Or Can I iterate over in psql? — Madhu
– Madhu, Commented Dec 1, 2017 at 20:33
You definitely should to specify the set of tools you can use. BTW look at \gexec metacommand (introduced since 9.6 version) — Abelisto
– Abelisto, Commented Dec 2, 2017 at 11:27

Daniel Vérité · Accepted Answer · 2017-12-02 15:57:41Z

Not being superuser means the export can't be done in a server-side DO block.

It could be done client-side in any programming language that can talk to the database, or assuming a psql-only environment, it's possible to generate a list of \copy statements with an SQL query.

As an example of the latter, assuming the unique output filenames are built from the ID column, something like this should work:

SELECT format('\copy (%s) TO ''file-%s.csv'' CSV', query, id) FROM table_with_queries;

The result of this query should be put into a file in a format such that it can be directly included into psql, like this:

\pset format unaligned \pset tuples_only on -- \g with an argument treats it as an output file. SELECT format('\copy (%s) TO ''file-%s.csv'' CSV', query, id) FROM table_with_queries \g /tmp/commands.sql \i /tmp/commands.sql

As a sidenote, that process cannot be managed with the \gexec meta-command introduced in PG 9.6, because \copy itself is a meta-command. \gexec iterates only on SQL queries, not on meta-commands. Otherwise the whole thing could be done by a single \gexec invocation.

@MadhuraMhatre: yes. It assumes that you expect a different output file for each query.

Gholamali Irani · Accepted Answer · 2017-12-01 19:57:50Z

You may use a function like: (IF your problem is the code)

DECLARE rec RECORD; BEGIN FOR rec IN SELECT id, query FROM table_name LOOP EXECUTE('COPY (' || rec.query || ') TO ' || QUOTE_LITERAL('d:/csv' || rec.id || '.csv') || ' CSV'); END LOOP; END;

for permission problem, You should use some places on server that you have writing access to them (or request from vendor).

Unfortunately I dont have superuser permissions. Can I use \copy command in anonymous block? Or Can I iterate over in psql?

Collectives™ on Stack Overflow

Export data in file in Postgres

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related