Re: Good Delimiter for copy command

On Thu, 12 Feb 2009, SHARMILA JOTHIRAJAH wrote:

> Im not using this for loading postgres data to postgres. Im trying this
> method to load my Oracle data to postgresql tables.... just trying to
> migrate my data from oracle to postgresql

The point other posts here were trying to make to you is that the ideal
situation is one where the source and destination databases support
escaping the delimiter in the same fashion. You might be able to arrange
that, depends on how you're dumping the data from Oracle.

If not, when you can't get matching delimiter escapes, there is no such
thing as a universal delimiter for moves between databases like this.
You have to look at your source data to figure out what characters aren't
used.

For example, "~" and "`" are good characters to consider because they
don't show up much in regular text. But if your database contained
programming source code, those would be awful delimiter choices. "@" used
to be a potential delimiter choice, nowadays too many databases have
e-mail addresses in them for that to work anymore.

Here's what you can do: pick one delimiter. Start with "~" say. Dump
your data. Now, search the result for the *other* delimiter you might use
(maybe "`"), using something like grep. If it doesn't show up, that other
delimiter will work for you. Otherwise, swap delimiters and try again.

The other option here that can avoid all sorts of headaches is to use
pgloader: http://pgfoundry.org/projects/pgloader/ which will save the
rows that are rejected for some reason, which is usually what happens when
there's a delimiter issue. You can then edit those by hand to work around
random odd delimiter problems.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD