Writing the output of sql query to a file

Question

I have an SQL query:

SELECT 'DROP TABLE ' || tablename
  FROM pg_catalog.pg_tables where tablename like 'r_395%';

whose output is this:

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

I am using entity manager to execute this query:

StringBuffer query = new StringBuffer();

query.append("SELECT 'DROP TABLE ' || tablename
                FROM pg_catalog.pg_tables where tablename like 'r_395%'");            
entityManager.createNativeQuery(query.toString()).executeUpdate();

I want to write this output to a file. If it was a mere a text file I would use filewriter and buffered writer. But now it is an SQL generated file, I am a little bit confused. Is there a way to achieve this in Java?

Answer 1

If you want to write a file locally (on the machine of the database) there is also the very fast and simple COPY for a case like this:

COPY $$SELECT 'DROP TABLE ' || tablename
         FROM pg_catalog.pg_tables
        WHERE tablename ~~ 'r_395%'$$ TO /path/to/myfile;

I use dollar-quoting here, to avoid escaping the single quotes.

Answer 2

A very similar solution to using SQL COPY (see the answer of Erwin Brandstetter) is to use the psql meta command \copy. The difference here is that you do not need local access on the database box. The output of SQL COPY can only be written to a location on the database box where the system user postgres has write access. With psql \copy you can write the output to a location on the client.

Connect to the database with psql and execute the command

psql -h host -p port -U user database
\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'

Or pass the command directly to psql with the -c parameter:

psql -h host -p port -U user -c "\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'" database

HTH