Thread: COPy command question
| Hi, A question about the Postgresql's COPY command. This is the syntax of this command from the manual COPY tablename [ ( column [, ...] ) ] |
On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: > 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? "STDIN" just means from the same place as the rest of the SQL has come from. For example, if you're using JDBC, it would be possible do generate a string containing: COPY tbl (col1,col2) FROM STDIN WITH CSV; 1,2 4,7 12,37 \. and execute() the whole string. There appear to be patches[1] available so you can stream directly into the database rather than having to generate a large strings to pass in. -- Sam http://samason.me.uk/ [1] http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
Yes should work perfectly as suggested by Sam,
chk this for jdbc support:
http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
Sam Mason wrote:
chk this for jdbc support:
http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
Sam Mason wrote:
On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: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?"STDIN" just means from the same place as the rest of the SQL has come from. For example, if you're using JDBC, it would be possible do generate a string containing: COPY tbl (col1,col2) FROM STDIN WITH CSV; 1,2 4,7 12,37 \. and execute() the whole string. There appear to be patches[1] available so you can stream directly into the database rather than having to generate a large strings to pass in.
-- Thanks & Regards Kedar Parikh Netcore Solutions Pvt. Ltd. Tel: +91 (22) 6662 8135 Mob: +91 9819634734 Email: kedar@netcore.co.in Web: www.netcore.co.in
On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH
<sharmi_jo@yahoo.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.
| 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.marlowe@gmail.com> wrote: From: Scott Marlowe <scott.marlowe@gmail.com> |