Re: ora2pg and invalid command \N

On 03/16/2018 11:36 AM, Charlin Barak wrote:
> Thanks for your response.
>
> the NULL values from Oracle were indeed replaced by \N in the data
> output. How do I go about loading this file? I hope I do not have to
> temporary replace \N with a string in the flat file and then later
> update in Postgres.

That should not be necessary:

https://www.postgresql.org/docs/10/static/sql-copy.html

"NULL

Specifies the string that represents a null value. The default is
\N (backslash-N) in text format, and an unquoted empty string in CSV
format. You might prefer an empty string even in text format for cases
where you don't want to distinguish nulls from empty strings. This
option is not allowed when using binary format."

To solve this is going to require more information. From my previous post:

What was the complete command you used to generate the data file?

Can we see the complete error?

Also a section of the data file that contains the data that caused the
error?

>
> Thanks.
>
> 102     48299   50  C       3      \N
> 103     48299   \N  G       3      45
> ...
> ...
>
> On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
>
>
>
> 2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak(at)gmail(dot)com
> <mailto:charlinbarak(at)gmail(dot)com>>:
>
> Hi,
> I'm using ora2pg to migrate our Oracle database to Postgres. I
> was able to generate the data file using TYPE=COPY but when I
> attempted to load the file via psql, I got lots of "invalid
> command \N" errors. The resolution on the internet was not clear
> and was  not ora2pg related. How do I resolve this issue? Can
> someone provide some guidance?
>
>
> \N is symbol for NULL. but \cmd are psql commands too. This behave
> means so psql lost synchronization and try to eval data like
> commands. psql import is tolerant - that means so source of this
> issue is lost usually. Try to import data with option ON_ERROR_STOP
>
> https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error
> <https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error>
>
> Regards
>
> Pavel
>
>
> Thanks.
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com