Thread: ora2pg and invalid command \N
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?On 03/16/2018 10:12 AM, Charlin Barak wrote: > 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 What was the complete command you used to generate the data file? > the file via psql, I got lots of "invalid command \N" errors. The Can we see the complete error? Also a section of the data file that contains the data that caused the error? > resolution on the internet was not clear and was not ora2pg related. > How do I resolve this issue? Can someone provide some guidance? > > Thanks. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak@gmail.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.
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.Thanks.
103 48299 \N G 3 45
...
...
On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak@gmail.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 RegardsPavelThanks.
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.stehule@gmail.com > <mailto:pavel.stehule@gmail.com>> wrote: > > > > 2018-03-16 18:12 GMT+01:00 Charlin Barak <charlinbarak@gmail.com > <mailto:charlinbarak@gmail.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.klaver@aklaver.com
On Fri, Mar 16, 2018 at 8:28 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > 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? I pinged the original author, and he thinks it might be because of a nested user defined type. If that's the case, you should try with current development version (https://github.com/darold/ora2pg/archive/master.zip) where the issue should be fixed. If not, you should try to open an issue on https://github.com/darold/ora2pg/issues.