pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Casey Allen Shobe <cshobe@secureworks.net>
Cc: pgsql-php@postgresql.org
Subject: Re: Moving data from other databases
Date: Tue, 27 Aug 2002 22:27:10 -0700
Message-ID: <3D6C5F2E.4060700@joeconway.com> (raw)
References: <200208280107.43674.cshobe@secureworks.net>

Casey Allen Shobe wrote:
> I have a large amount of data in DB2 and Microsoft SQL databases that I would 
> like to copy to new tables in Postgres, primarily for testing and 
> proof-of-concept purposes.
> 
> I have a Linux/PHP installation supporting all three, and was wondering if PHP 
> would be a good medium for working this task.

I think that depends on how much data is "a large amount" and how much 
patience you have ;-)

Seriously, I think you would be better off using MSSQL's BCP (bulk copy) 
program to create a tab delimited file and then use PostgreSQL COPY to 
import the data. The process would be something like:

- Use bcp to export data file
- Possibly use sed/awk/your-favorite-text-processing-program to adjust
   the output (only *if* necessary) -- things to watch out for here are
   delimiter characters (e.g. tabs) embedded in you data which need
   escaping of some sort, and NULL values. I can't remember how much
   control BCP gives you in representing nulls, but pgsql COPY allows it
   to be specified.
- Create tables in pgsql; do not create indexes or foreign keys if
   possible at this point.
- Import data using COPY
- Create indexes and refint constraints
- Vacuum analyze

This process should be orders of magnatude faster than querying the data 
out, looping through row-by-row, building and executing insert 
statements, etc. I suppose you could build the import file using PHP and 
then use Postgres COPY, but it would still be more work and alot slower 
than using vendor provided export utilities.

I'm not familiar with DB2, but I'd guess it comes with some sort of bulk 
export utility also.

HTH,

Joe






view thread (2+ messages)

Message-ID: <3D6C5F2E.4060700@joeconway.com>
Permalink:  ../3D6C5F2E.4060700@joeconway.com/
Also on:    postgresql.org/message-id/3D6C5F2E.4060700@joeconway.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: mail@joeconway.com, cshobe@secureworks.net
  Subject: Re: Moving data from other databases
  In-Reply-To: <3D6C5F2E.4060700@joeconway.com>
  References: <200208280107.43674.cshobe@secureworks.net> <3D6C5F2E.4060700@joeconway.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox