Thread: 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 externally 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
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 asVinod\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 Mahindrapolicy 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 asVinod\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 \ 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 TechMahindra 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 asVinod\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 TechMahindra 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!
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 asVinod\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 \ 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 TechMahindra 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 KrishnaGottuparthi <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 defaultpostgresql format which requires the use of \ for escapes like \r, \n, \t, \\, \012 and for the default NULL specificationof \N. If you use this format it is therefore your responsibility to ensure that any literal \ characters in the data are escapedas \\, 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 Mahindrapolicy 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>;
BiswaRanjan 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
necessarychanges, 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
Mahindrapolicy 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>;
BiswaRanjan 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>;
BiswaRanjan 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
necessarychanges, 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
Mahindrapolicy 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 +
> > In what format are you dumping the DB2 data and with what specifications e.g. quoting? > DB2's export command quotes the data with "". So while loading, shouldn't that take care of delimiter-in-the-data issue ?
On 5/3/19 7:35 AM, Ravi Krishna wrote: >> >> In what format are you dumping the DB2 data and with what specifications e.g. quoting? >> > > DB2's export command quotes the data with "". So while loading, shouldn't that take care of delimiter-in-the-data issue? > In the original post the only info was: "We have dump from DB2 and trying to upload it Postgre." That is a little vague, I would prefer more concrete information before proposing a solution. -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, May 3, 2019 at 9:35 AM Ravi Krishna <ravikrishna@mail.com> wrote:
>
> In what format are you dumping the DB2 data and with what specifications e.g. quoting?
>
DB2's export command quotes the data with "". So while loading, shouldn't that take care of delimiter-in-the-data issue ?
I don't think we've seen enough representative data to know exactly what the backslash is doing. It doesn't appear to be an escape, based on the sole example I've seen it appears to be a data separator between first name and last name.
It seems increasingly likely to me that you might not be in a position where the COPY command in PostgreSQL can handle loading the database, at least not without some help. You might have to write a program to clean up the data and format it for PostgreSQL.
I've spent a lot of time over the years migrating data from one platform to another, you have to know EXACTLY what data you currently have and what format you need it turned into before you can figure out how to do the transformation.
--
Mike Nolan
> > I don't think we've seen enough representative data to know exactly what the backslash is doing. It doesn't appear tobe an escape, based on the sole example I've seen it appears to be a data separator between first name and last name. > > It seems increasingly likely to me that you might not be in a position where the COPY command in PostgreSQL can handleloading the database, at least not without some help. You might have to write a program to clean up the data and formatit for PostgreSQL. > > I've spent a lot of time over the years migrating data from one platform to another, you have to know EXACTLY what datayou currently have and what format you need it turned into before you can figure out how to do the transformation. > -- > Mike Nolan Fully agreed. I have informed Guntry via email that he has to provide more information before we can help further.
Hi Team, Here we go. I will give the problem in more detail Step 1:We get the dump from DB2 and this dump is flat file which can be csv,txt Step2:There is table in PostGre where we are suppose to upload the dump Step3:We are using copy command to upload dump to the table using (COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER'|'; ) Step 4:In the above step we are using delimiter because the data is separated (:) in the flat which we have received fromthe flat file Problem Statement:We are able to upload the data from the flat file which we got from the DB2 but few data the data consistof " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table forCustomerName as Vinod\G but we see VinodG(slash is missed). Possible Solution: We can replace "\" with "\\" but if the file is in too large we cannot open it(we can replace if the fileis medium or small) Expectation: We need a command or utility which can upload the data as it is (for example if Vinod\G then we should see inPostGre as Vinod\G but not VinodG) Hope Iam detail this time :-) Regards, Vinod -----Original Message----- From: Ravi Krishna <ravikrishna@mail.com> Sent: Friday, May 3, 2019 8:43 PM To: Michael Nolan <htfoot@gmail.com> Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Guntry Vinod <GV00619735@TechMahindra.com>; pgsql-general@postgresql.org;Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com> Subject: Re: Back Slash \ issue > > I don't think we've seen enough representative data to know exactly what the backslash is doing. It doesn't appear tobe an escape, based on the sole example I've seen it appears to be a data separator between first name and last name. > > It seems increasingly likely to me that you might not be in a position where the COPY command in PostgreSQL can handleloading the database, at least not without some help. You might have to write a program to clean up the data and formatit for PostgreSQL. > > I've spent a lot of time over the years migrating data from one platform to another, you have to know EXACTLY what datayou currently have and what format you need it turned into before you can figure out how to do the transformation. > -- > Mike Nolan Fully agreed. I have informed Guntry via email that he has to provide more information before we can help further. ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy 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. ============================================================================================================================
> > Hope Iam detail this time :-) > Unfortunately still not enough. Can you post sample of the data here. And what command you used in DB2. Pls post the SQLused in DB2 to dump the data.
On 5/3/19 9:05 AM, Guntry Vinod wrote: > Hi Team, > > Here we go. I will give the problem in more detail > > Step 1:We get the dump from DB2 and this dump is flat file which can be csv,txt The above is what we need information on: 1) Is it output as CSV or text? 2) What are the parameters used to output the data in either case?. In other words, is string quoting used, the field delimiter, etc. > Step2:There is table in PostGre where we are suppose to upload the dump > Step3:We are using copy command to upload dump to the table using (COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER'|'; ) > Step 4:In the above step we are using delimiter because the data is separated (:) in the flat which we have received fromthe flat file > > Problem Statement:We are able to upload the data from the flat file which we got from the DB2 but few data the data consistof " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table forCustomerName as Vinod\G but we see VinodG(slash is missed). > > Possible Solution: We can replace "\" with "\\" but if the file is in too large we cannot open it(we can replace if thefile is medium or small) > > Expectation: We need a command or utility which can upload the data as it is (for example if Vinod\G then we should seein PostGre as Vinod\G but not VinodG) > > Hope Iam detail this time :-) > > Regards, > Vinod > > > > > -----Original Message----- > From: Ravi Krishna <ravikrishna@mail.com> > Sent: Friday, May 3, 2019 8:43 PM > To: Michael Nolan <htfoot@gmail.com> > Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Guntry Vinod <GV00619735@TechMahindra.com>; pgsql-general@postgresql.org;Venkatamurali Krishna Gottuparthi <VG00114307@TechMahindra.com>; Biswa Ranjan Dash <BD00617837@TechMahindra.com> > Subject: Re: Back Slash \ issue > >> >> I don't think we've seen enough representative data to know exactly what the backslash is doing. It doesn't appear tobe an escape, based on the sole example I've seen it appears to be a data separator between first name and last name. >> >> It seems increasingly likely to me that you might not be in a position where the COPY command in PostgreSQL can handleloading the database, at least not without some help. You might have to write a program to clean up the data and formatit for PostgreSQL. >> >> I've spent a lot of time over the years migrating data from one platform to another, you have to know EXACTLY what datayou currently have and what format you need it turned into before you can figure out how to do the transformation. >> -- >> Mike Nolan > > Fully agreed. I have informed Guntry via email that he has to provide more information before we can help further. > ============================================================================================================================ > > Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the TechMahindra 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/3/19 9:05 AM, Guntry Vinod wrote: > Hi Team, > > Here we go. I will give the problem in more detail > > Step 1:We get the dump from DB2 and this dump is flat file which can be csv,txt > Step2:There is table in PostGre where we are suppose to upload the dump > Step3:We are using copy command to upload dump to the table using (COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER'|'; ) Should have been in my previous post. The answer to whether Step 1 is text or CSV is important as that determines the way you use the COPY command above. As has been pointed out upstream using COPY assuming text(as you are doing above) on a CSV file will create issues. > Step 4:In the above step we are using delimiter because the data is separated (:) in the flat which we have received fromthe flat file > > Problem Statement:We are able to upload the data from the flat file which we got from the DB2 but few data the data consistof " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table forCustomerName as Vinod\G but we see VinodG(slash is missed). > > Possible Solution: We can replace "\" with "\\" but if the file is in too large we cannot open it(we can replace if thefile is medium or small) > > Expectation: We need a command or utility which can upload the data as it is (for example if Vinod\G then we should seein PostGre as Vinod\G but not VinodG) > > Hope Iam detail this time :-) > > Regards, > Vinod > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Others have suggested you're in a Windows environment, that might limit your options. How big is the file, is it possible to copy it to another server to manipulate it?
--
Mike Nolan
Hi, On Fri, May 3, 2019 at 11:20 AM Michael Nolan <htfoot@gmail.com> wrote: > > > > I'm still not clear what the backslash is for, it is ONLY to separate first and last name? Can you change it to some othercharacter? > > Others have suggested you're in a Windows environment, that might limit your options. How big is the file, is it possibleto copy it to another server to manipulate it? Why not use something like Perl to process the data and then feed the processed file to PostgreSQL? Thank you. > -- > Mike Nolan
Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error:
An error has occurred:
Column not found in pgSet: rolcatupdate
Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks
Looping Nikhil and Shiva who are from Mainframe, DB2. Nikhil/Shiva I am trying explain the problem to the team but there few questions which needs your intervention. Regards, Vinod -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: Friday, May 3, 2019 9:47 PM To: Guntry Vinod <GV00619735@TechMahindra.com>; Ravi Krishna <ravikrishna@mail.com>; Michael Nolan <htfoot@gmail.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/3/19 9:05 AM, Guntry Vinod wrote: > Hi Team, > > Here we go. I will give the problem in more detail > > Step 1:We get the dump from DB2 and this dump is flat file which can > be csv,txt Step2:There is table in PostGre where we are suppose to > upload the dump Step3:We are using copy command to upload dump to the > table using (COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER > '|'; ) Should have been in my previous post. The answer to whether Step 1 is text or CSV is important as that determines the wayyou use the COPY command above. As has been pointed out upstream using COPY assuming text(as you are doing above) on aCSV file will create issues. > Step 4:In the above step we are using delimiter because the data is > separated (:) in the flat which we have received from the flat file > > Problem Statement:We are able to upload the data from the flat file which we got from the DB2 but few data the data consistof " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table forCustomerName as Vinod\G but we see VinodG(slash is missed). > > Possible Solution: We can replace "\" with "\\" but if the file is in > too large we cannot open it(we can replace if the file is medium or > small) > > Expectation: We need a command or utility which can upload the data as > it is (for example if Vinod\G then we should see in PostGre as Vinod\G > but not VinodG) > > Hope Iam detail this time :-) > > Regards, > Vinod > > > > -- Adrian Klaver adrian.klaver@aklaver.com ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy 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/3/19 10:05 AM, Guntry Vinod wrote: > Hi Team, > > Here we go. I will give the problem in more detail > > Step 1:We get the dump from DB2 and this dump is flat file which can be csv,txt > Step2:There is table in PostGre where we are suppose to upload the dump > Step3:We are using copy command to upload dump to the table using (COPY <<TableName>> from 'C:\Data_Dump\ABC.txt' DELIMITER'|'; ) > Step 4:In the above step we are using delimiter because the data is separated (:) in the flat which we have received fromthe flat file > > Problem Statement:We are able to upload the data from the flat file which we got from the DB2 but few data the data consistof " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table forCustomerName as Vinod\G but we see VinodG(slash is missed). > > Possible Solution: We can replace "\" with "\\" but if the file is in too large we cannot open it(we can replace if thefile is medium or small) > > Expectation: We need a command or utility which can upload the data as it is (for example if Vinod\G then we should seein PostGre as Vinod\G but not VinodG) > > So everything works except the backslashes disappear, correct? As many have said all you need to do is double the backslashes, and apparently you done that on small files. How have you done that? Perhaps with an editor? For the large files you must use a tool (perl awk, even a trivial C program) to double the backslashes.
Guys,
Do you know what does this message mean?
POSTGRES_FSM_RELATIONS=CRITICAL: DB control (host:xxx) fsm relations used: 76628 of 80000 (96%)
Is this caused by someone deleting a bunch of old data and not vacuuming?
Thanks!
From: Julie Nishimura <juliezain@hotmail.com>
Sent: Friday, May 3, 2019 9:39 AM
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Pgadmin III
Sent: Friday, May 3, 2019 9:39 AM
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Pgadmin III
Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error:
An error has occurred:
Column not found in pgSet: rolcatupdate
Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks
Julie Nishimura <juliezain@hotmail.com> writes:
> Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error:
> An error has occurred:
> Column not found in pgSet: rolcatupdate
> Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks
Development of pgAdmin 3 stopped some time ago, so I'm not surprised that
it can't cope with recent PG servers. You should use pgAdmin 4 instead.
Don't know anything about its minor versions, though.
pgAdmin-specific questions are best directed to one of the pgAdmin lists,
eg pgadmin-support@lists.postgresql.org
regards, tom lane
Thanks!
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, May 3, 2019 11:25 AM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: Pgadmin III
Sent: Friday, May 3, 2019 11:25 AM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: Pgadmin III
Julie Nishimura <juliezain@hotmail.com> writes:
> Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error:
> An error has occurred:
> Column not found in pgSet: rolcatupdate
> Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks
Development of pgAdmin 3 stopped some time ago, so I'm not surprised that
it can't cope with recent PG servers. You should use pgAdmin 4 instead.
Don't know anything about its minor versions, though.
pgAdmin-specific questions are best directed to one of the pgAdmin lists,
eg pgadmin-support@lists.postgresql.org
regards, tom lane
> Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error:
> An error has occurred:
> Column not found in pgSet: rolcatupdate
> Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks
Development of pgAdmin 3 stopped some time ago, so I'm not surprised that
it can't cope with recent PG servers. You should use pgAdmin 4 instead.
Don't know anything about its minor versions, though.
pgAdmin-specific questions are best directed to one of the pgAdmin lists,
eg pgadmin-support@lists.postgresql.org
regards, tom lane
there is a port of pgadmin3 from bigSQL, support postgresql up to 10. pgadmin3 in debian buster support also postgesql 10. https://metadata.ftp-master.debian.org/changelogs/main/p/pgadmin3/pgadmin3_1.22.2-5_changelog what version of pgadmin3 did you use? pgadmin3_1.22 should support postgresql 9.6. On 03.05.19 20:29, Julie Nishimura wrote: > Thanks! > > ------------------------------------------------------------------------ > *From:* Tom Lane <tgl@sss.pgh.pa.us> > *Sent:* Friday, May 3, 2019 11:25 AM > *To:* Julie Nishimura > *Cc:* Adrian Klaver; pgsql-general@postgresql.org > *Subject:* Re: Pgadmin III > > Julie Nishimura <juliezain@hotmail.com> writes: >> Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and I am getting this error: >> An error has occurred: >> Column not found in pgSet: rolcatupdate > >> Do you know which version of Pgadmin should I use to avoid this? I am on windows 7. Thanks > > Development of pgAdmin 3 stopped some time ago, so I'm not surprised that > it can't cope with recent PG servers. You should use pgAdmin 4 instead. > Don't know anything about its minor versions, though. > > pgAdmin-specific questions are best directed to one of the pgAdmin lists, > eg pgadmin-support@lists.postgresql.org > > regards, tom lane
On 5/3/19 10:14 AM, Julie Nishimura wrote: > Guys, > Do you know what does this message mean? > POSTGRES_FSM_RELATIONS=CRITICAL: DB control (host:xxx) fsm relations used: 76628 of 80000 (96%) What is generating above? Postgres version? FYI, it seems you piggybacked this post(along with the Pgadmin III post) on another thread 'Back Slash \ issue'. That tends to mess with the message threading, so it better to start a new thread. > > Is this caused by someone deleting a bunch of old data and not vacuuming? > > > Thanks! > > > > > ------------------------------------------------------------------------ > *From:* Julie Nishimura <juliezain@hotmail.com> > *Sent:* Friday, May 3, 2019 9:39 AM > *Cc:* Adrian Klaver; pgsql-general@postgresql.org > *Subject:* Pgadmin III > Hello, I am trying to connect to PostgreSQL 9.6.2 using PGAdmin III, and > I am getting this error: > An error has occurred: > Column not found in pgSet: rolcatupdate > > Do you know which version of Pgadmin should I use to avoid this? I am on > windows 7. Thanks -- Adrian Klaver adrian.klaver@aklaver.com