title: how to export data from postgresql to .csv file using jdbc?
body: <p>I want to export data in a table by date, so I write a java program using jdbc. </p>
  
  <pre><code>  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 &gt;= '" + thisDayString + 
                          "' AND date_time &lt; '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv  delimiter ','";
  
                  stmt.executeQuery(query);  
              //System.out.println(query);
  
              }
  
          } catch(Exception e) {
              e.printStackTrace();
              stmt = null;
          }
      }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>query ="\COPY (SELECT * FROM signals WHERE date_time &gt;= '" + thisDayString + 
                              "' AND date_time &lt; '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv  delimiter ','";
  </code></pre>
  
  <p>Dose anyone know how to make the program execute or solve the problem about invalid escape character in "\copy"?</p>