Re: Export tab delimited from mysql to postgres. - Mailing list pgsql-sql
From | Theo Galanakis |
---|---|
Subject | Re: Export tab delimited from mysql to postgres. |
Date | |
Msg-id | 82E30406384FFB44AFD1012BAB230B5505F40D43@shiva.au.lpint.net Whole thread Raw |
In response to | Export tab delimited from mysql to postgres. (Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>) |
Responses |
Re: Export tab delimited from mysql to postgres.
Re: Export tab delimited from mysql to postgres. |
List | pgsql-sql |
<p><font size="2">Thanks for all your comments,</font><p> <font size="2">I have beent trying the insert within a transactionblock, however it does not seem to reduce the time it takes to process each records. Mind you there are 80 columnand the insert statement explicitly defines the column to insert into.</font><p> <font size="2">I need any tipI can get help me transform the text file into a format postgres copy will successfully read. </font><p><font size="2">Hereis sample of the current format of a mysql tab delimited dump..</font><p><font size="2">columnA columnB </font><br/><font size="2">1 What a day!</font><br /><font size="2">2 What a week it has</font><br/><font size="2">been!</font><br /><font size="2">3 What the!</font><p><font size="2">As you cansee row 2 has a value that holds a CR value which ends up wrapping around onto the third line. Postgres copy command doesnot like this and mysql is unable to replace the value with another type of delimiter, like a \r.</font><p><font size="2">SoI gather I have to some how manually replace the carriage return with something postgres understand \r... </font><p><fontsize="2">columnA columnB </font><br /><font size="2">1 What a day!</font><br /><font size="2">2 What a week it has \r been!</font><br /><font size="2">3 What the!</font><p><font size="2">Howdo I do this without getting a text file that looks like this </font><br /><font size="2">1 What a day!\r\n2 What a week it has \r been!\r\n3 What the!\r\n</font><p><font size="2">Any help wouldbe appreciated.</font><p><font size="2">Theo</font><p><font size="2">-----Original Message-----</font><br /><font size="2">From:Christopher Browne [<a href="mailto:cbbrowne@acm.org">mailto:cbbrowne@acm.org</a>] </font><br /><font size="2">Sent:Tuesday, 12 October 2004 10:46 AM</font><br /><font size="2">To: pgsql-sql@postgresql.org</font><br /><fontsize="2">Subject: Re: [SQL] Export tab delimited from mysql to postgres.</font><br /><p><font size="2">Quoth Theo.Galanakis@lonelyplanet.com.au(Theo Galanakis):</font><br /><font size="2">> Could you provide a example ofhow to do this?</font><br /><font size="2">></font><br /><font size="2">> I actually ended up exporting thedata as Insert statements,</font><br /><font size="2">> which strips out cf/lf within varchars. However it takes aneternity </font><br /><font size="2">> to import 200,000 records... 24 hours infact???? Is this normal?</font><p><fontsize="2">I expect that this results from each INSERT being a separate transaction.</font><p><font size="2">Ifyou put a BEGIN at the start and a COMMIT at the end, you'd doubtless see an ENORMOUS improvement.</font><p><fontsize="2">That's not even the _big_ improvement, either. The _big_ improvement would involve reformattingthe data so that you could use the COPY statement, which is _way_ faster than a bunch of INSERTs. Take a lookat the documentation to see the formatting that is needed:</font><p><font size="2"><a href="http://techdocs.postgresql.org/techdocs/usingcopy.php" target="_blank">http://techdocs.postgresql.org/techdocs/usingcopy.php</a></font><br/><font size="2"><a href="http://www.faqs.org/docs/ppbook/x5504.htm"target="_blank">http://www.faqs.org/docs/ppbook/x5504.htm</a></font><br /><fontsize="2"><a href="http://www.postgresql.org/docs/7.4/static/sql-copy.html" target="_blank">http://www.postgresql.org/docs/7.4/static/sql-copy.html</a></font><br/><font size="2">-- </font><br /><fontsize="2">output = ("cbbrowne" "@" "ntlug.org") <a href="http://www3.sympatico.ca/cbbrowne/lsf.html" target="_blank">http://www3.sympatico.ca/cbbrowne/lsf.html</a></font><br/><font size="2">Question: How many surrealists doesit take to change a light bulb?</font><p><font size="2">Answer: Two, one to hold the giraffe, and the other to fill thebathtub</font><br /><font size="2"> with brightly colored machine tools.</font><p><font size="2">---------------------------(endof broadcast)---------------------------</font><br /><font size="2">TIP 2: you canget off all lists at once with the unregister command</font><br /><font size="2"> (send "unregister YourEmailAddressHere"to majordomo@postgresql.org)</font><table><tr><td bgcolor="#ffffff"><font color="#000000">______________________________________________________________________<br/>This email, including attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br />communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the contentowner.<br /></font></td></tr></table>