Обсуждение: ERROR: COPY escape must be a single one-byte character(multi-delimiter appears to work on Postgres 9.0 but does not on Postgres 9.2)
Hello,
I have a Talend enterprise job that loads data into a PostgreSQL database via the COPY command. When migrating to a new server this command fails with the following error message: org.postgresql.util.PSQLException:ERROR: COPY escape must be a single one-byte character
The thing is, I looked over the documentation for both Postgres 9.0 and 9.2. Both documentations say that multi-byte delimiters are not allowed. So I'm very confused on why this job works perfectly on Postgres 9.0 but not on 9.2.
I am unable to edit this Talend job, as it's very old and we do not have the source code for the job anymore. I am unable to see what the actual delimiter is. I am also unable to see exactly how the COPY command is being run, such as whether it's pushing directly to the server via the Postgres driver, or if it's created a temporary CSV file somewhere and then loading the data into the server. I believe the reason we have multi byte delimiters setup is due to the use of various special characters in a few of the columns for multiple tables.
I am not aware of any edits to the source code of the old 9.0 Postgres server.
The reason we are migrating servers is due to the end of life for CentOS 5. The new server runs CentOS 7. I believe that both servers are using the default Postgres versions that come in the default CentOS repositories. I know for sure that the CentOS 7 server is indeed running the default Postgres version, as I installed it myself through yum.
Any help would be greatly appreciated.
Also, is there a way to copy the old Postgres server, dependencies, and executables to our new server, in case the source was modified?
Brandon Ragland
Software Engineer
BREAKFRONT SOFTWARE
Office: 704.688.4085 | Mobile: 240.608.9701 | Fax: 704.973.0607
I have a Talend enterprise job that loads data into a PostgreSQL database via the COPY command. When migrating to a new server this command fails with the following error message: org.postgresql.util.PSQLException:ERROR: COPY escape must be a single one-byte character
The thing is, I looked over the documentation for both Postgres 9.0 and 9.2. Both documentations say that multi-byte delimiters are not allowed. So I'm very confused on why this job works perfectly on Postgres 9.0 but not on 9.2.
I am unable to edit this Talend job, as it's very old and we do not have the source code for the job anymore. I am unable to see what the actual delimiter is. I am also unable to see exactly how the COPY command is being run, such as whether it's pushing directly to the server via the Postgres driver, or if it's created a temporary CSV file somewhere and then loading the data into the server. I believe the reason we have multi byte delimiters setup is due to the use of various special characters in a few of the columns for multiple tables.
I am not aware of any edits to the source code of the old 9.0 Postgres server.
The reason we are migrating servers is due to the end of life for CentOS 5. The new server runs CentOS 7. I believe that both servers are using the default Postgres versions that come in the default CentOS repositories. I know for sure that the CentOS 7 server is indeed running the default Postgres version, as I installed it myself through yum.
Any help would be greatly appreciated.
Also, is there a way to copy the old Postgres server, dependencies, and executables to our new server, in case the source was modified?
Brandon Ragland
Software Engineer
BREAKFRONT SOFTWARE
Office: 704.688.4085 | Mobile: 240.608.9701 | Fax: 704.973.0607
On 11/13/19 4:40 PM, Brandon Ragland wrote: > Hello, > > I have a Talend enterprise job that loads data into a PostgreSQL > database via the COPY command. When migrating to a new server this > command fails with the following error message: > org.postgresql.util.PSQLException:ERROR: COPY escape must be a single > one-byte character Does the Postgres log offer anymore information? Can you crank up the error level to get more info? Are the encodings for the Postgres server/OS different ? > > The thing is, I looked over the documentation for both Postgres 9.0 and > 9.2. Both documentations say that multi-byte delimiters are not allowed. > So I'm very confused on why this job works perfectly on Postgres 9.0 but > not on 9.2. > > I am unable to edit this Talend job, as it's very old and we do not have > the source code for the job anymore. I am unable to see what the actual > delimiter is. I am also unable to see exactly how the COPY command is > being run, such as whether it's pushing directly to the server via the > Postgres driver, or if it's created a temporary CSV file somewhere and > then loading the data into the server. I believe the reason we have > multi byte delimiters setup is due to the use of various special > characters in a few of the columns for multiple tables. > > I am not aware of any edits to the source code of the old 9.0 Postgres > server. > > The reason we are migrating servers is due to the end of life for CentOS > 5. The new server runs CentOS 7. I believe that both servers are using > the default Postgres versions that come in the default CentOS > repositories. I know for sure that the CentOS 7 server is indeed running > the default Postgres version, as I installed it myself through yum. > > Any help would be greatly appreciated. > > Also, is there a way to copy the old Postgres server, dependencies, and > executables to our new server, in case the source was modified? > > Brandon Ragland > Software Engineer > BREAKFRONT SOFTWARE > Office: 704.688.4085 | Mobile: 240.608.9701 | Fax: 704.973.0607 -- Adrian Klaver adrian.klaver@aklaver.com
>Adrian Klaver wrote: >On 11/13/19 4:40 PM, Brandon Ragland wrote: >> Hello, >> >> I have a Talend enterprise job that loads data into a PostgreSQL >> database via the COPY command. When migrating to a new server this >> command fails with the following error message: >> org.postgresql.util.PSQLException:ERROR: COPY escape must be a single >> one-byte character > >Does the Postgres log offer anymore information? > >Can you crank up the error level to get more info? > >Are the encodings for the Postgres server/OS different ? What is the COPY command you're using? To extend Adrian's good questions, have you really examined the line in question carefully with a tool that will show you the true characters being used? You didn't show the COPY command, but to me, this sounds like you have a "fancy" character happening. The place I most often see this is with double quotes: U+0022 normal ASCII " (1 byte) U+201c left double quote (2 bytes!) Depending on the font used, those 2 can look very much alike and there are far more characters that can be mixed up like this. The insidious ones for me are the whitespace chars, e.g. 0xa0. I find this happens frequently when the source of a copy-paste is from another program, e.g. MS-Word; our ticket system did this for awhile too. :( HTH, Kevin This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information.If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
> I am unable to edit this Talend job, as it's very old and we do not have the > source code for the job anymore. I am unable to see what the actual delimiter Compiled talend jobs produce jars file with java .class files in which the SQL statements are in plain text. You should be at least able to get the copy statement (which is plain text SQL), and also being able to modify it. On Wed, Nov 13, 2019 at 07:40:59PM -0500, Brandon Ragland wrote: > Hello, > > I have a Talend enterprise job that loads data into a PostgreSQL database via > the COPY command. When migrating to a new server this command fails with the > following error message: org.postgresql.util.PSQLException:ERROR: COPY escape > must be a single one-byte character > > The thing is, I looked over the documentation for both Postgres 9.0 and 9.2. > Both documentations say that multi-byte delimiters are not allowed. So I'm very > confused on why this job works perfectly on Postgres 9.0 but not on 9.2. > > I am unable to edit this Talend job, as it's very old and we do not have the > source code for the job anymore. I am unable to see what the actual delimiter > is. I am also unable to see exactly how the COPY command is being run, such as > whether it's pushing directly to the server via the Postgres driver, or if it's > created a temporary CSV file somewhere and then loading the data into the > server. I believe the reason we have multi byte delimiters setup is due to the > use of various special characters in a few of the columns for multiple tables. > > I am not aware of any edits to the source code of the old 9.0 Postgres server. > > The reason we are migrating servers is due to the end of life for CentOS 5. The > new server runs CentOS 7. I believe that both servers are using the default > Postgres versions that come in the default CentOS repositories. I know for sure > that the CentOS 7 server is indeed running the default Postgres version, as I > installed it myself through yum. > > Any help would be greatly appreciated. > > Also, is there a way to copy the old Postgres server, dependencies, and > executables to our new server, in case the source was modified? > > Brandon Ragland > Software Engineer > BREAKFRONT SOFTWARE > Office: 704.688.4085 | Mobile: 240.608.9701 | Fax: 704.973.0607 -- nicolas