how to export data from postgresql to .csv file using jdbc?

Question

I want to export data in a table by date, so I write a java program using jdbc.

  public void exportData(Connection conn,String filename) {

        Statement stmt;
        String query;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE); 

            SimpleDateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss");
            Date startDate = dateFormat.parse("2014-08-21 00:00:00");
            Date endDate = dateFormat.parse("2014-08-22 00:00:00");

            Calendar startCalendar = Calendar.getInstance();
            startCalendar.setTime(startDate);

            Calendar endCalendar = Calendar.getInstance();
            endCalendar.setTime(endDate);

            Calendar thisDayCalendar = startCalendar;

            while(!thisDayCalendar.after(endCalendar)){
                Date thisDayDate = thisDayCalendar.getTime();
                thisDayCalendar.add(Calendar.DATE, 1);
                Date nextDayDate = thisDayCalendar.getTime();

                String thisDayString = dateFormat.format(thisDayDate);
                String nextDayString = dateFormat.format(nextDayDate);

                SimpleDateFormat dateFormat1 = new SimpleDateFormat ("yyyy-MM-dd");
                String fileDateString = dateFormat1.format(thisDayDate);

                //For comma separated file
                query ="COPY (SELECT * FROM signals WHERE date_time >= '" + thisDayString + 
                        "' AND date_time < '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv  delimiter ','";

                stmt.executeQuery(query);  
            //System.out.println(query);

            }

        } catch(Exception e) {
            e.printStackTrace();
            stmt = null;
        }
    }

This programming is running on a server while the database is another sever, I connect to the database in this program.But when I run the program on server, I got an error that only superuser can COPY to or from a file:

org.postgresql.util.PSQLException: ERROR: must be superuser to COPY to or from a file
  Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
        at DBase.exportData(ExportSignal.java:85)
        at ExportSignal.main(ExportSignal.java:23)

I searched solutions online and tried to use psql '\copy' command but when compiling,another error said '\' is invalid escape character in java so I wrote "double slashes COPY" which made syntax error when running the program.

query ="\COPY (SELECT * FROM signals WHERE date_time >= '" + thisDayString + 
                            "' AND date_time < '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv  delimiter ','";

Dose anyone know how to make the program execute or solve the problem about invalid escape character in "\copy"?

http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html — Konstantin V. Salikhov, Nov 26 '14 at 16:50
following link may help you : http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file — Bhavin Panchani, Nov 26 '14 at 17:06
Thanks @BhavinPanchani, I follow the instruction, "\copy' works by command line but in this java program. — chenchenmomo, Nov 26 '14 at 17:50
@Chenlu `\copy` is only available in `psql` tool. only `COPY` is available to other connection methods. — Igor Romanchenko, Nov 26 '14 at 19:22

score 14 · Answer 1 · edited Dec 16 '19 at 16:55

14

I would recommend using CopyManager for this.

Connection connection = DriverManager.getConnection(url);
CopyManager copyManager = new CopyManager((BaseConnection) connection);
File file = new File("/tmp/output.csv");
FileOutputStream fileOutputStream = new FileOutputStream(file);

//and finally execute the COPY command to the file with this method:
copyManager.copyOut("COPY (" + query + ") TO STDOUT WITH (FORMAT CSV, HEADER)", fileOutputStream);

This method does not require you to be superuser and will correctly format the output text as CSV

edited Dec 16 '19 at 16:55

keeping_it_simple

399
1
8
29

answered Sep 22 '15 at 22:52

SlimPDX

564
1
4
18

How to get de name of columns from database? – Pena Pintada Jan 10 '17 at 17:58
1

@PenaPintada http://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server – SlimPDX Jan 10 '17 at 19:49
@SlimPDX. I referred the link to get the column names. But i want to know all the columns in csv to have respective column names also.Please guide on how to achieve it – vinod hy Sep 18 '19 at 06:16
I executed this before i execute my actual query to fetch the column names as per the link **String columnQuery = "SELECT column_name FROM information_schema.columns WHERE table_name = 'Employee_Table'"; copyManager.copyOut("COPY (" + columnQuery + ") TO STDOUT WITH (FORMAT CSV)", fileOutputStream);** But this inserts all the column names vertically in the csv file. I am not expecting this. Instead, column names should be inserted horizontally. Please help me. I am struggling with this – vinod hy Sep 18 '19 at 06:16

score 2 · Accepted Answer · answered Nov 26 '14 at 16:55

2

The error shows ERROR: must be superuser to COPY to or from a file

It seems that the current user is not having enough permissions to write the file. Provide permissions to the write location for the user or execute the program from a user account which has sufficient privileges to write to the file.

answered Nov 26 '14 at 16:55

vembutech

1,596
1
9
15

I can use \copy to execute this query instead of COPY without the superuser permission on command line, but in java program, it is illegal escape character to using '\copy', but when I try 'double slashes copy", there is a syntax error on postgresql exception. So I wonder if any method can solve this.. – chenchenmomo Nov 26 '14 at 17:40
1

@Chenlu please see my method that I have added as an answer. – SlimPDX Sep 22 '15 at 22:53

how to export data from postgresql to .csv file using jdbc?

2 Answers2