RE: Re: Load Text File into DB

From: Jeff Eckermann <jeckermann(at)verio(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: RE: Re: Load Text File into DB
Date: 2001-04-16 14:16:44
Message-ID: 08CD1781F85AD4118E0800A0C9B8580B094A27@NEZU
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

COPY expects tab delimiters by default, so specifying them is not strictly
necessary in this case.
Specifying "null as xxx" is a good idea, because COPY interprets an empty
field as an empty string, which will cause failure for non text-type fields.
A couple of other things to watch out for when bulk-loading data with COPY
(based on my painful experiences):
* Embedded delimiters, i.e. tabs in your data. Start by grepping for
lines with more than the expected number of tabs.
* Fields ending with a backslash. Sounds unlikely, but this has
happened to me several times. The result is that your delimiter is escaped,
shifting all of your following fields down by one. This will either cause
COPY to fail (because of data type mismatches), or messed up data in that
record.
* For files imported from a Windows machine, better edit out the
carriage returns.

> -----Original Message-----
> From: Chris Jones [SMTP:chris(dot)jones(at)romancompany(dot)com]
> Sent: Friday, April 13, 2001 1:27 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Re: Load Text File into DB
>
> This is what I have used with success..
>
> copy {table name} from '{delimited file name}' using delimiters '\t' with
> null as '';
>
> Of course, don't put in the brackets and you can use whatever null
> character
> you might need...
>
> Hope this helps
>
> Chris
> Karl J. Stubsjoen wrote in message <9b20s1$1b3d$1(at)news(dot)tht(dot)net>...
> >Hello,
> >
> >I have a tab delimited text file located on the server and would like to
> >load this into a table. How do I accomplish this?
> >
> >Thanks!
> >
> >Karl
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2001-04-16 14:39:50 Re: bpchar type
Previous Message Justin Clift 2001-04-16 14:14:45 Re: OS Crypt()