From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: Export tab delimited from mysql to postgres.
Date: Mon, 11 Oct 2004 20:46:02 -0400
Message-ID: <m3d5zoyd9x.fsf@knuth.knuth.cbbrowne.com> (raw)
References: <82E30406384FFB44AFD1012BAB230B5505F40D3E@shiva.au.lpint.net>
Quoth Theo.Galanakis@lonelyplanet.com.au (Theo Galanakis):
> Could you provide a example of how to do this?
>
> I actually ended up exporting the data as Insert statements,
> which strips out cf/lf within varchars. However it takes an eternity
> to import 200,000 records... 24 hours infact???? Is this normal?
I expect that this results from each INSERT being a separate
transaction.
If you put a BEGIN at the start and a COMMIT at the end, you'd
doubtless see an ENORMOUS improvement.
That's not even the _big_ improvement, either. The _big_ improvement
would involve reformatting the data so that you could use the COPY
statement, which is _way_ faster than a bunch of INSERTs. Take a look
at the documentation to see the formatting that is needed:
http://techdocs.postgresql.org/techdocs/usingcopy.phphttp://www.faqs.org/docs/ppbook/x5504.htmhttp://www.postgresql.org/docs/7.4/static/sql-copy.html
--
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/lsf.html
Question: How many surrealists does it take to change a light bulb?
Answer: Two, one to hold the giraffe, and the other to fill the bathtub
with brightly colored machine tools.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Export tab delimited from mysql to postgres.
In-Reply-To: <m3d5zoyd9x.fsf@knuth.knuth.cbbrowne.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