From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Best way to import data in postgresl (not "COPY") |
Date: | 2009-07-22 22:49:13 |
Message-ID: | 20090722224913.GU5407@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jul 22, 2009 at 08:24:22PM +0200, Denis BUCHER wrote:
> SELECT ... FROM ODBC source
> foreach row {
> INSERT INTO postgresql
> }
>
> The problem is that this method is very slow...
>
> Does someone has a better suggestion ?
Using COPY[1] is normally the preferred solution to getting data into PG
fast. Some languages make this easier than others, if you can generate
SQL that looks like:
COPY table (col1,col2) FROM STDIN WITH CSV;
13,hello
42,"text with,comma"
\.
then you should be in luck---just bung this off to the ODBC driver
as is and all should good. If you need to copy more than will fit
in a string, arrange to put a few thousand rows in each batch, and
generate them and insert them one-at-a-time in a transaction. Using
tab-delimited mode (drop the WITH CSV) is possible, but most languages
will provide library code for generating CSV files and hence will
probably be easier to get correct.
--
Sam http://samason.me.uk/
[1] http://www.postgresql.org/docs/current/static/sql-copy.html
From | Date | Subject | |
---|---|---|---|
Next Message | Robert James | 2009-07-22 22:50:07 | Re: Can LIKE under utf8 use INDEXes? |
Previous Message | Andreas Wenk | 2009-07-22 22:45:41 | Re: Can LIKE under utf8 use INDEXes? |