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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
