Re: COPy command question

Thanks all
This is my simple java code
public class copy{
  public static void main(String[] args) throws Exception
    {
      Connection connection1=null;
      Connection connection2=null;
      Statement stmt;
      String driverName1="org.postgresql.Driver";
      Class.forName(driverName2);
      connection1=DriverManager.getConnection(args[0],args[1],args[2]);
      pstmt=connection1.prepareStatement("select employee_id  ||','||employee_name from Employee");
      ResultSet rs1=pstmt.executeQuery();

      while (rs1.next())
          {
           System.out.println(rs1.getString(1));
    }
      stmt.close();
           connection1.close();
    }
}

And I pipe this to the psql like this
 ant/bin/ant copy -emacs | sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d'|psql -c "copy employee from STDIN WITH null 'NULL' DELIMITER ','" EMP

ant/bin/ant copy -emacs ----- I run it using ant
 sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d ---- trim the unnecessary ant outputs like the 1st 2 lines and last 2 ines and any blank lines using 'sed'
so that my final output will be just the data with a 'comma' delimiter that I feed it to the psql COPY command...

It seems to work... I havent checked the performance for big tables...Im not sure how it scales for big tables... Do you know any other way of improving my java code to retrieve the data fast or in batches ?

Also does COPY treat timestamp & LOBs data different?

Thanks
Sharmila

--- On Thu, 2/12/09, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Subject: Re: [GENERAL] COPy command question
To: sharmi_jo(at)yahoo(dot)com
Cc: "General postgres mailing list" <pgsql-general(at)postgresql(dot)org>
Date: Thursday, February 12, 2009, 1:35 PM

On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH
<sharmi_jo(at)yahoo(dot)com> wrote:
> Hi,
> A question about the Postgresql's COPY command.
>
> This is the syntax of this command from the manual
>
> COPY tablename [ ( column [, ...] ) ]
> FROM { 'filename' | STDIN }
> [ [ WITH ]
> .....
> I want to migrate my tables from Oracle to Postgres.
> The COPY FROM command can take input from 'file' or
'STDIN'.
> Is it possible for the COPY command to take its input from a
> java program(which contains the oracle resultset) or any other way?

If that java program can provide direct input to postgresql then yes.
If everything has to be a prepared statement etc then no. Assuming
your java framework allows you just throw input at the database, you'd
be able to just give it the input line by line.

> I know I could get the Oracle rows in a csv format but
> Im trying to get it done without any file in between ?
>
> In short is it possible to use this 'COPY' command to migrate my
tables'
> data from Oracle to Postgresql without using any file
> in between?

Sure, I can do it in PHP. I've done it in PHP. If your java
connectors have the facility to throw raw sql at pgsql then it should
work.