Back Slash \ issue
Hi Team,
We have dump from DB2 and trying to upload it Postgre.
The records which has \(back ward) are failing to import.
Can anyone in the team help us to resolve the issue.
Can you also let us know if we are posting the question to the right team.
Regards,
Vinod
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
On 5/2/19 5:20 AM, Guntry Vinod wrote:
Hi Team,
We have dump from DB2 and trying to upload it Postgre.
What program did you use to take the dump?
How are you importing the data into Postgres(note the 's')?
What is the exact error message?
The records which has \(back ward) are failing to import.
Can anyone in the team help us to resolve the issue.
Can you also let us know if we are posting the question to the right team.
Regards,
Vinod
============================================================================================================================
Disclaimer:� This message and the information contained herein is
proprietary and confidential and subject to the Tech Mahindra policy
statement, you may review the policy at
http://www.techmahindra.com/Disclaimer.html externally
http://tim.techmahindra.com/tim/disclaimer.html internally within
TechMahindra.============================================================================================================================
--
Adrian Klaver
adrian.klaver@aklaver.com
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>> We have dump from DB2 and trying to upload it Postgre.</div>
<div> </div>
<div>DB2 export command has an option to export it as CSV which quotes data so that any embedded</div>
<div>lines or special characters inside the data is treated fine. Does the csv format has quotes enclosing it ?</div></div></body></html>
On 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?
Please show the command you use to import into Postgres.
Also the error message you get.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/2/19 7:34 AM, Adrian Klaver wrote:
On 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
> We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?Please show the command you use to import into Postgres.
Also the error message you get.
Oops was not paying attention to sender, ignore above.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Adrian\Kiran,
Below is the issue.
We are migrating from Db2 to Postgre.
The Db2 dump consists of back Slash \ with in the data [dump] , but postgre is not escaping the Slash.
For example if name consist of Vinod\G after the inserting the dump the value in postgre is VinodG where as I need it as Vinod\G.
Since the dump is huge data I can't replace \ within the data to escape Slash.
We are using import utility provided by Postgre.
Can you please give me any solution in this regard?.
@kiran-let me also try solution you provided in the below mail.
Regards,
Vinod
_______________________________________
From: Adrian Klaver [adrian.klaver@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikrishna@mail.com; Guntry Vinod
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash
Subject: Re: Back Slash \ issue
On 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?
Please show the command you use to import into Postgres.
Also the error message you get.
--
Adrian Klaver
adrian.klaver@aklaver.com
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
On 5/2/19 8:19 AM, Guntry Vinod wrote:
Hi Adrian\Kiran,
Below is the issue.
We are migrating from Db2 to Postgre.
The Db2 dump consists of back Slash \ with in the data [dump] , but postgre is not escaping the Slash.
For example if name consist of Vinod\G after the inserting the dump the value in postgre is VinodG where as I need it as Vinod\G.
Since the dump is huge data I can't replace \ within the data to escape Slash.
We are using import utility provided by Postgre.
Are you talking about COPY or some other utility?
Can you please give me any solution in this regard?.
In order for a solution to be found we will need more explicit
information on what you are doing.
@kiran-let me also try solution you provided in the below mail.
Regards,
Vinod
_______________________________________
From: Adrian Klaver [adrian.klaver@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikrishna@mail.com; Guntry Vinod
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash
Subject: Re: Back Slash \ issueOn 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?Please show the command you use to import into Postgres.
Also the error message you get.--
Adrian Klaver
adrian.klaver@aklaver.com
============================================================================================================================Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/2/19 9:19 AM, Guntry Vinod wrote:
Hi Adrian\Kiran,
Below is the issue.
We are migrating from Db2 to Postgre.
The Db2 dump consists of back Slash \ with in the data [dump] , but postgre is not escaping the Slash.
For example if name consist of Vinod\G after the inserting the dump the value in postgre is VinodG where as I need it as Vinod\G.
Have you tried somlething like
sed 's/\\/\\\\/g' db2dump > db2dump.fixed
Show quoted text
Since the dump is huge data I can't replace \ within the data to escape Slash.
We are using import utility provided by Postgre.
Can you please give me any solution in this regard?.
@kiran-let me also try solution you provided in the below mail.
Regards,
Vinod
_______________________________________
From: Adrian Klaver [adrian.klaver@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikrishna@mail.com; Guntry Vinod
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash
Subject: Re: Back Slash \ issueOn 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?Please show the command you use to import into Postgres.
Also the error message you get.--
Adrian Klaver
adrian.klaver@aklaver.com
============================================================================================================================Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
Have you tried adding
ESCAPE '*\*' to the PostgreSQL COPY command?
https://www.postgresql.org/docs/10/sql-copy.html
On Thu, May 2, 2019 at 12:54 PM Rob Sargent <robjsargent@gmail.com> wrote:
On 5/2/19 9:19 AM, Guntry Vinod wrote:
Hi Adrian\Kiran,
Below is the issue.
We are migrating from Db2 to Postgre.
The Db2 dump consists of back Slash \ with in the data [dump] , but
postgre is not escaping the Slash.
For example if name consist of Vinod\G after the inserting the dump the
value in postgre is VinodG where as I need it as Vinod\G.
Have you tried somlething like
sed 's/\\/\\\\/g' db2dump > db2dump.fixed
Since the dump is huge data I can't replace \ within the data to escape
Slash.
We are using import utility provided by Postgre.
Can you please give me any solution in this regard?.
@kiran-let me also try solution you provided in the below mail.
Regards,
Vinod
_______________________________________
From: Adrian Klaver [adrian.klaver@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikrishna@mail.com; Guntry Vinod
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi;Biswa Ranjan Dash
Subject: Re: Back Slash \ issue
On 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes data
so that any embedded
lines or special characters inside the data is treated fine. Does the
csv format has quotes enclosing it ?Please show the command you use to import into Postgres.
Also the error message you get.--
Adrian Klaver
adrian.klaver@aklaver.com============================================================================================================================
Disclaimer: This message and the information contained herein is
proprietary and confidential and subject to the Tech Mahindra policy
statement, you may review the policy at
http://www.techmahindra.com/Disclaimer.html <
http://www.techmahindra.com/Disclaimer.html> externally
http://tim.techmahindra.com/tim/disclaimer.html <
http://tim.techmahindra.com/tim/disclaimer.html> internally within
TechMahindra.============================================================================================================================
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
Hi Team,
We are using the below command
COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
Regards,
Vinod
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, May 2, 2019 8:58 PM
To: Guntry Vinod <GV00619735@TechMahindra.com>; ravikrishna@mail.com
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com>
Subject: Re: Back Slash \ issue
On 5/2/19 8:19 AM, Guntry Vinod wrote:
Hi Adrian\Kiran,
Below is the issue.
We are migrating from Db2 to Postgre.
The Db2 dump consists of back Slash \ with in the data [dump] , but postgre is not escaping the Slash.
For example if name consist of Vinod\G after the inserting the dump the value in postgre is VinodG where as I need it as Vinod\G.
Since the dump is huge data I can't replace \ within the data to escape Slash.
We are using import utility provided by Postgre.
Are you talking about COPY or some other utility?
Can you please give me any solution in this regard?.
In order for a solution to be found we will need more explicit information on what you are doing.
@kiran-let me also try solution you provided in the below mail.
Regards,
Vinod
_______________________________________
From: Adrian Klaver [adrian.klaver@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikrishna@mail.com; Guntry Vinod
Cc: pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi;
Biswa Ranjan Dash
Subject: Re: Back Slash \ issueOn 5/2/19 7:30 AM, ravikrishna@mail.com wrote:
We have dump from DB2 and trying to upload it Postgre.
DB2 export command has an option to export it as CSV which quotes
data so that any embedded lines or special characters inside the data
is treated fine. Does the csv format has quotes enclosing it ?Please show the command you use to import into Postgres.
Also the error message you get.--
Adrian Klaver
adrian.klaver@aklaver.com
======================================================================
======================================================Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
======================================================================
======================================================
--
Adrian Klaver
adrian.klaver@aklaver.com
"Guntry" == Guntry Vinod <GV00619735@TechMahindra.com> writes:
Guntry> Hi Team,
Guntry> We are using the below command
Guntry> COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
COPY in postgresql expects one of two data formats; since you did not
specify CSV, in this case it's expecting the default postgresql format
which requires the use of \ for escapes like \r, \n, \t, \\, \012 and
for the default NULL specification of \N.
If you use this format it is therefore your responsibility to ensure
that any literal \ characters in the data are escaped as \\, and that
any literal appearance of the delimiter character or a newline is also
escaped.
See https://www.postgresql.org/docs/current/sql-copy.html under "Text
format".
--
Andrew (irc:RhodiumToad)
Hi Andrew,
So you mean to say we need to replace \\ in data. If so the data what we receive is huge chunk(we cannot open in notepad++ also) .
If we can pass the CSV instead of .txt or any other format. Do we have any solution. if Yes Can you please give me some example.
Many Thanks,
Vinod
-----Original Message-----
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
Sent: Friday, May 3, 2019 1:37 PM
To: Guntry Vinod <GV00619735@TechMahindra.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; ravikrishna@mail.com; pgsql-general@postgresql.org; Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com>
Subject: Re: Back Slash \ issue
"Guntry" == Guntry Vinod <GV00619735@TechMahindra.com> writes:
Guntry> Hi Team,
Guntry> We are using the below command
Guntry> COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
COPY in postgresql expects one of two data formats; since you did not specify CSV, in this case it's expecting the default postgresql format which requires the use of \ for escapes like \r, \n, \t, \\, \012 and for the default NULL specification of \N.
If you use this format it is therefore your responsibility to ensure that any literal \ characters in the data are escaped as \\, and that any literal appearance of the delimiter character or a newline is also escaped.
See https://www.postgresql.org/docs/current/sql-copy.html under "Text format".
--
Andrew (irc:RhodiumToad)
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
El día Friday, May 03, 2019 a las 08:45:02AM +0000, Guntry Vinod escribió:
Hi Andrew,
So you mean to say we need to replace \\ in data. If so the data what we receive is huge chunk(we cannot open in notepad++ also) .
...
Hi Guntry,
What about piping the data on a Linux or any other UNIX (or even Cygwin
on Windows) through a sed-Kommand to do the necessary changes, like
echo 'bla\foo' | sed 's/\\/\\\\/'
bla\\foo
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 70 years
of war preparation against Russia. -- PEACE instead of NATO !
The postgre is running on Windows platform.
-----Original Message-----
From: Matthias Apitz <guru@unixarea.de>
Sent: Friday, May 3, 2019 2:32 PM
To: Guntry Vinod <GV00619735@TechMahindra.com>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; pgsql-general@postgresql.org; Adrian Klaver <adrian.klaver@aklaver.com>; ravikrishna@mail.com; Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com>
Subject: Re: Back Slash \ issue
El día Friday, May 03, 2019 a las 08:45:02AM +0000, Guntry Vinod escribió:
Hi Andrew,
So you mean to say we need to replace \\ in data. If so the data what we receive is huge chunk(we cannot open in notepad++ also) .
...
Hi Guntry,
What about piping the data on a Linux or any other UNIX (or even Cygwin on Windows) through a sed-Kommand to do the necessary changes, like
echo 'bla\foo' | sed 's/\\/\\\\/'
bla\\foo
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 70 years of war preparation against Russia. -- PEACE instead of NATO !
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
Team,
We had also tried importing the data by converting the data to a CSV file using
\copy TABLE_NAME FROM 'G:\DB_Backup\FILE.csv' (format csv, null '\N');
Regards,
Biswa
-----Original Message-----
From: Guntry Vinod <GV00619735@TechMahindra.com>
Sent: Friday, May 3, 2019 2:35 PM
To: Matthias Apitz <guru@unixarea.de>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; pgsql-general@postgresql.org; Adrian Klaver <adrian.klaver@aklaver.com>; ravikrishna@mail.com; Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com>
Subject: RE: Back Slash \ issue
The postgre is running on Windows platform.
-----Original Message-----
From: Matthias Apitz <guru@unixarea.de>
Sent: Friday, May 3, 2019 2:32 PM
To: Guntry Vinod <GV00619735@TechMahindra.com>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; pgsql-general@postgresql.org; Adrian Klaver <adrian.klaver@aklaver.com>; ravikrishna@mail.com; Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com>
Subject: Re: Back Slash \ issue
El día Friday, May 03, 2019 a las 08:45:02AM +0000, Guntry Vinod escribió:
Hi Andrew,
So you mean to say we need to replace \\ in data. If so the data what we receive is huge chunk(we cannot open in notepad++ also) .
...
Hi Guntry,
What about piping the data on a Linux or any other UNIX (or even Cygwin on Windows) through a sed-Kommand to do the necessary changes, like
echo 'bla\foo' | sed 's/\\/\\\\/'
bla\\foo
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 70 years of war preparation against Russia. -- PEACE instead of NATO !
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html> externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html> internally within TechMahindra.
============================================================================================================================
El día Friday, May 03, 2019 a las 09:04:34AM +0000, Guntry Vinod escribió:
The postgre is running on Windows platform.
Maybe you haven't read completely through the post you are top posting
on. It was clear to me (from the used file name syntax) that you are on
Windows; that's why I said:
What about piping the data on a Linux or any other UNIX (or even Cygwin on Windows)
through a sed-Kommand to do the necessary changes, like ...
i.e you could use Windows for this. Or transfer the data for processing
to a Linux system, and back for loading.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 70 years
of war preparation against Russia. -- PEACE instead of NATO !
On 5/2/19 10:48 PM, Guntry Vinod wrote:
Please do not top post. Inline/bottom posting is the preferred style on
this list.
Hi Team,
We are using the below command
COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
The above shows what you are doing on the input into Postgres.
We still do not know how you are dumping the data from DB2.
In what format are you dumping the DB2 data and with what specifications
e.g. quoting?
Regards,
Vinod
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, May 3, 2019 at 06:55:55AM -0700, Adrian Klaver wrote:
On 5/2/19 10:48 PM, Guntry Vinod wrote:
Please do not top post. Inline/bottom posting is the preferred style on this
list.Hi Team,
We are using the below command
COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';
The above shows what you are doing on the input into Postgres.
We still do not know how you are dumping the data from DB2.In what format are you dumping the DB2 data and with what specifications
e.g. quoting?
On thing the original poster might be missing is that the copy DELIMITER
is used between fields, while backslash is used as an escape before a
single character. While it might be tempting to try to redefine the
escape character with the copy ESCAPE keyword, that keyword only works
in CSV mode.
The Postgres COPY format is very reliable and able to dump/reload _any_
data sequence. Many commercial data dump implementations are simpler
but are not able to be as reliable.
The bottom line is that you are going to need to double the backslashes
unless you move to CSV mode.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +On Fri, May 3, 2019 at 10:04:44AM -0400, Bruce Momjian wrote:
On thing the original poster might be missing is that the copy DELIMITER
is used between fields, while backslash is used as an escape before a
single character. While it might be tempting to try to redefine the
escape character with the copy ESCAPE keyword, that keyword only works
in CSV mode.The Postgres COPY format is very reliable and able to dump/reload _any_
data sequence. Many commercial data dump implementations are simpler
but are not able to be as reliable.
For example, if you are using | as a delimiter, how do you represent a
literal | in the data? You have to use an escape character before it,
and that is what backslash does, and if you have a backslash in your
data, you have to use a backslash before it too. CSV has a similar
problem with double-quotes in double-quoted strings, and this is handled
by default by using two double-quotes.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
