Re: Back Slash \ issue
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 <> 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 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 consist of " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table for CustomerName 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) 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.
RE: Back Slash \ issue
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 Sent: Friday, May 3, 2019 9:47 PM To: Guntry Vinod ; Ravi Krishna ; Michael Nolan Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash 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 <> 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 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 consist of " \".For example if the > CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre > table for CustomerName 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.kla...@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.
Re: Back Slash \ issue
Hi, On Fri, May 3, 2019 at 11:20 AM Michael Nolan 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 other character? > > 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? Why not use something like Perl to process the data and then feed the processed file to PostgreSQL? Thank you. > -- > Mike Nolan
Re: Back Slash \ issue
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 other character? 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
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 <> 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 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 consist of " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table for CustomerName 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.kla...@aklaver.com
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 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 <> 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 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 consist of " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table for CustomerName 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 -Original Message- From: Ravi Krishna Sent: Friday, May 3, 2019 8:43 PM To: Michael Nolan Cc: Adrian Klaver ; Guntry Vinod ; pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash 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 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 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 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.kla...@aklaver.com
Re: Back Slash \ issue
> > 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 SQL used in DB2 to dump the data.
RE: Back Slash \ issue
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 <> 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 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 consist of " \".For example if the CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre table for CustomerName 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 -Original Message- From: Ravi Krishna Sent: Friday, May 3, 2019 8:43 PM To: Michael Nolan Cc: Adrian Klaver ; Guntry Vinod ; pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash 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 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 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 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.
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 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 Fully agreed. I have informed Guntry via email that he has to provide more information before we can help further.
Re: Back Slash \ issue
On Fri, May 3, 2019 at 9: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 ? > 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
Re: Back Slash \ 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.kla...@aklaver.com
Re: Back Slash \ issue
> > 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 ?
Re: Back Slash \ issue
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 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 +
Re: Back Slash \ issue
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 <> 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 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 +
Re: Back Slash \ issue
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 <> 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.kla...@aklaver.com
Re: Back Slash \ issue
El día Friday, May 03, 2019 a las 09:04:34AM +, 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, ✉ g...@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 !
RE: Back Slash \ issue
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 Sent: Friday, May 3, 2019 2:35 PM To: Matthias Apitz Cc: Andrew Gierth ; pgsql-gene...@postgresql.org; Adrian Klaver ; ravikris...@mail.com; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash Subject: RE: Back Slash \ issue The postgre is running on Windows platform. -Original Message- From: Matthias Apitz Sent: Friday, May 3, 2019 2:32 PM To: Guntry Vinod Cc: Andrew Gierth ; pgsql-gene...@postgresql.org; Adrian Klaver ; ravikris...@mail.com; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash Subject: Re: Back Slash \ issue El día Friday, May 03, 2019 a las 08:45:02AM +, 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, ✉ g...@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.
RE: Back Slash \ issue
The postgre is running on Windows platform. -Original Message- From: Matthias Apitz Sent: Friday, May 3, 2019 2:32 PM To: Guntry Vinod Cc: Andrew Gierth ; pgsql-gene...@postgresql.org; Adrian Klaver ; ravikris...@mail.com; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash Subject: Re: Back Slash \ issue El día Friday, May 03, 2019 a las 08:45:02AM +, 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, ✉ g...@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.
Re: Back Slash \ issue
El día Friday, May 03, 2019 a las 08:45:02AM +, 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, ✉ g...@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 !
RE: Back Slash \ issue
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 Sent: Friday, May 3, 2019 1:37 PM To: Guntry Vinod Cc: Adrian Klaver ; ravikris...@mail.com; pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash Subject: Re: Back Slash \ issue >>>>> "Guntry" == Guntry Vinod writes: Guntry> Hi Team, Guntry> We are using the below command Guntry> COPY <> 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.
Re: Back Slash \ issue
> "Guntry" == Guntry Vinod writes: Guntry> Hi Team, Guntry> We are using the below command Guntry> COPY <> 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)
RE: Back Slash \ issue
Hi Team, We are using the below command COPY <> from 'C:\Data_Dump\ABC.txt' DELIMITER '|'; Regards, Vinod -Original Message- From: Adrian Klaver Sent: Thursday, May 2, 2019 8:58 PM To: Guntry Vinod ; ravikris...@mail.com Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi ; Biswa Ranjan Dash 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.kla...@aklaver.com] > Sent: 02 May 2019 20:04 > To: ravikris...@mail.com; Guntry Vinod > Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; > Biswa Ranjan Dash > Subject: Re: Back Slash \ issue > > On 5/2/19 7:30 AM, ravikris...@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.kla...@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.kla...@aklaver.com
Re: Back Slash \ issue
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 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.kla...@aklaver.com] > > Sent: 02 May 2019 20:04 > > To: ravikris...@mail.com; Guntry Vinod > > Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; > Biswa Ranjan Dash > > Subject: Re: Back Slash \ issue > > > > On 5/2/19 7:30 AM, ravikris...@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.kla...@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!
Re: Back Slash \ issue
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.kla...@aklaver.com] Sent: 02 May 2019 20:04 To: ravikris...@mail.com; Guntry Vinod Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash Subject: Re: Back Slash \ issue On 5/2/19 7:30 AM, ravikris...@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.kla...@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.
RE: Back Slash \ issue
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.kla...@aklaver.com] Sent: 02 May 2019 20:04 To: ravikris...@mail.com; Guntry Vinod Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash Subject: Re: Back Slash \ issue On 5/2/19 7:30 AM, ravikris...@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.kla...@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.
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.kla...@aklaver.com] Sent: 02 May 2019 20:04 To: ravikris...@mail.com; Guntry Vinod Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa Ranjan Dash Subject: Re: Back Slash \ issue On 5/2/19 7:30 AM, ravikris...@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.kla...@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.kla...@aklaver.com
Re: Back Slash \ issue
On 5/2/19 7:34 AM, Adrian Klaver wrote: On 5/2/19 7:30 AM, ravikris...@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.kla...@aklaver.com
Re: Back Slash \ issue
On 5/2/19 7:30 AM, ravikris...@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.kla...@aklaver.com
Re: Back Slash \ issue
> 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 ?
Re: Back Slash \ issue
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.kla...@aklaver.com