Re: Best way to import data in postgresl (not "COPY")

Denis BUCHER wrote:
> Hello,
>
> I have a system that must each day import lots of data from another one.
> Our system is in postgresql and we connect to the other via ODBC.
>
> Currently we do something like :
>
> SELECT ... FROM ODBC source
> foreach row {
> INSERT INTO postgresql
> }
>
> The problem is that this method is very slow...
>
> Does someone has a better suggestion ?
>
> Thanks a lot in advance !
>
> Denis
>

If you can prepare your statement it would run a lot faster, no idea if
odbc supports such things though.

so:

select ... from odbc...;
$q = prepare('insert into pg...')
foreach row {
   $q.params[0] = ..
   $q.params[1] = ..
   $q.execute;
}
commit;

(* if possible, make sure you are not commitiing each insert statement,
do them all the commit once at the end *)


If you cant prepare, you should try to build multi-value insert statements:

insert into pgtable (col1, col2, col3) values ('a', 'b', 'c'), ('d',
'e', 'f'), ('g','h','i'),...;

Or, you could look into dblink, dunno if it would be faster.

-Andy