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

2

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.

answered Dec 2, 2017 at 15:57

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

3 Comments

Madhu Over a year ago

Will it generate file for each of the row?

Daniel Vérité Over a year ago

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

Madhu Over a year ago

not for each query I want for each row from that query

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

0

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).

answered Dec 1, 2017 at 19:57

Gholamali Irani

4,3707 gold badges32 silver badges61 bronze badges

1 Comment

Madhu Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related