ERROR: invalid byte sequence for encoding UTF8: 0x00
Hi, I have a table with bytea column and trying to load the data using copy command. But Copy command is failing with "ERROR: invalid byte sequence for encoding UTF8: 0x00. Why postgresql is failing to load when data contains 0x00. How to resolve this error ? any workaround to load the data with 0x00 values ? Please advise. Thank you very much. Regards, Kiran
Issue with loading unicode characters with copy command
Hi, I have a UTF8 database and simple table with two columns (integer and varchar). Created a csv file with some multibyte characters and trying to perform load operation using the copy command. Database info: Postgresql database details: Name| Owner | Encoding | Collate | Ctype | Access privileges ---+--+--+++--- postgres | postgres | UTF8 | English_India.1252 | English_India.1252 | (Note: I also tried with collate utf8 and no luck) postgres=# set client_encoding='UTF8'; SET Table: create table public.test ( PKCOL integer not null, STR1 character varying(64) null, primary key( PKCOL )) csv contents: 1|"àáâãäåæçèéêëìíîï" After data loading, actual data is becoming à áâãäåæçèéêëìÃîï hex of this is - c2a1c2a2c2a3c2a4c2a5c2a6c2a7c2a8c2a9c2aac2abc2acc2aec2af The hex values are indeed the UTF-8 encodings of the characters in your expected string, and the presence of `C2` before each character is indicative of how UTF-8 represents certain characters. In UTF-8, characters from the extended Latin set (like `à`, `á`, `â`, etc.) are represented as two bytes. The first byte `C2` or `C3` indicates that this is a two-byte character, and the second byte specifies the character. For example: - `à` is represented as `C3 A0` - `á` is `C3 A1` - `â` is `C3 A2`, and so on. In this case, the `C2` byte is getting interpreted as a separate character and that is the likely reason that an `Â` (which corresponds to `C2`) is seen before each intended character. Looks like UTF-8 encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or Windows-1252, where each byte is treated as a separate character. Please advise. Thank you very much. Regards, Kiran
Re: Issue with loading unicode characters with copy command
Its UTF-8. Also verified the load file and its utf-8. Regards, Kiran On Fri, Jan 12, 2024 at 10:48 PM Adrian Klaver wrote: > On 1/12/24 07:23, Kiran K V wrote: > > Hi, > > > > > > I have a UTF8 database and simple table with two columns (integer and > > varchar). Created a csv file with some multibyte characters and trying > > to perform load operation using the copy command. > > The multibyte characters come from what character set? > > > > > __ __ > > > > Database info: > > > > Postgresql database details: > > > > Name| Owner | Encoding | Collate | > > Ctype| Access privileges > > > > > ---+--+--+++--- > > > > postgres | postgres | UTF8 | English_India.1252 | > > English_India.1252 | > > > > __ __ > > > > (Note: I also tried with collate utf8 and no luck) > > > > > > postgres=# set client_encoding='UTF8'; > > > > SET > > > > __ __ > > > > Table: > > > > create table public.test ( PKCOL integer not null, STR1 character > > varying(64) null, primary key( PKCOL )) > > > > > > > > csv contents: > > > > 1|"àáâãäåæçèéêëìíîï" > > > > __ __ > > > > After data loading, actual data is becoming > > > > à áâãäåæçèéêëìÃîï > > > > hex of this is - > > c2a1c2a2c2a3c2a4c2a5c2a6c2a7c2a8c2a9c2aac2abc2acc2aec2af > > > > __ __ > > > > The hex values are indeed the UTF-8 encodings of the characters in your > > expected string, and the presence of `C2` before each character is > > indicative of how UTF-8 represents certain characters. > > > > In UTF-8, characters from the extended Latin set (like `à`, `á`, `â`, > > etc.) are represented as two bytes. The first byte `C2` or `C3` > > indicates that this is a two-byte character, and the second byte > > specifies the character. For example: > > > > - `à` is represented as `C3 A0` > > > > - `á` is `C3 A1` > > > > - `â` is `C3 A2`, and so on. > > > > In this case, the `C2` byte is getting interpreted as a separate > > character and that is the likely reason that an `Â` (which corresponds > > to `C2`) is seen before each intended character. Looks like UTF-8 > > encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or > > Windows-1252, where each byte is treated as a separate character. > > > > > > Please advise. Thank you very much. > > > > > > Regards, > > > > Kiran > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Query related to Logical Replication using test_decoding and unchanged-toast-datum
Hi, I am currently using PostgreSQL version 16 and the test_decoding plugin to perform logical replication (using replication slots). I have a simple table with integer column and JSON column. When a non-JSON column is updated, the value "unchanged-toast-datum" for the JSON column is obtained. This shows that the value recorded in a TOAST table for a JSON column value has not changed and is as expected. could you please tell me whether PostgreSQL will truly log these values to WAL or not ? If not, what will be entered into WAL for the JSON column if it remains unchanged? Please let me know. Thank you very much. Regards, Kiran
Query regarding support of test_decoding and PGReplicationStream with Standby Logical Replication
Hi, I have a question regarding the new feature introduced in PostgreSQL 16 that enables logical replication from a standby server. Currently, we are using a standalone instance of PostgreSQL 16 and performing logical replication by creating replication slots and utilizing the JDBC replication stream API (PGReplicationStream) with the test_decoding plugin. Could you please confirm whether this approach of logical replication is supported from a standby system? Specifically, does the standby system support the test_decoding plugin and the JDBC PGReplicationStream API for streaming changes ? Reference to JDBC Logical replication - https://jdbc.postgresql.org/documentation/server-prepare/#example-96-create-logical-replication-stream Regards, Kiran
Query regarding support of test_decoding and PGReplicationStream with Standby Logical Replication
Hi, I have a question regarding the new feature introduced in PostgreSQL 16 that enables logical replication from a standby server. Currently, we are using a standalone instance of PostgreSQL 16 and performing logical replication by creating replication slots and utilizing the JDBC replication stream API (PGReplicationStream) with the test_decoding plugin. Could you please confirm whether this approach of logical replication is supported from a standby system? Specifically, does the standby system support the test_decoding plugin and the JDBC PGReplicationStream API for streaming changes ? Reference to JDBC Logical replication - https://jdbc.postgresql.org/documentation/server-prepare/#example-96-create-logical-replication-stream Regards, Kiran
Re: Query regarding support of test_decoding and PGReplicationStream with Standby Logical Replication
Hi Dinesh, Yes, that is correct. Regards, Kiran On Tue, Jul 15, 2025 at 11:43 PM DINESH NAIR wrote: > > Hi Kiran, > > Are we trying to perform logical replication from create replication slots > on a standby server and use logical decoding plugins(JDBC > PGReplicationStream API) to stream changes from the standby > Is my understanding correct? > > > > > Hi, > > I have a question regarding the new feature introduced in PostgreSQL 16 > that enables logical replication from a standby server. > > Currently, we are using a standalone instance of PostgreSQL 16 and > performing logical replication by creating replication slots and utilizing > the JDBC replication stream API (PGReplicationStream) with the > test_decoding plugin. > > Could you please confirm whether this approach of logical replication is > supported from a standby system? Specifically, does the standby system > support the test_decoding plugin and the JDBC PGReplicationStream API for > streaming changes ? > > Reference to JDBC Logical replication - > https://jdbc.postgresql.org/documentation/server-prepare/#example-96-create-logical-replication-stream > > Regards, > Kiran > > > > > Thanks > > Dinesh > > > -- > *From:* Kiran K V > *Sent:* Friday, July 11, 2025 11:09 PM > *To:* pgsql-gene...@postgresql.org > *Subject:* Query regarding support of test_decoding and > PGReplicationStream with Standby Logical Replication > > You don't often get email from kirankv.1...@gmail.com. Learn why this is > important <https://aka.ms/LearnAboutSenderIdentification> > Caution: This email was sent from an external source. Please verify the > sender’s identity before clicking links or opening attachments. > Hi, > > I have a question regarding the new feature introduced in PostgreSQL 16 > that enables logical replication from a standby server. > > Currently, we are using a standalone instance of PostgreSQL 16 and > performing logical replication by creating replication slots and utilizing > the JDBC replication stream API (PGReplicationStream) with the > test_decoding plugin. > > Could you please confirm whether this approach of logical replication is > supported from a standby system? Specifically, does the standby system > support the test_decoding plugin and the JDBC PGReplicationStream API for > streaming changes ? > > Reference to JDBC Logical replication - > https://jdbc.postgresql.org/documentation/server-prepare/#example-96-create-logical-replication-stream > > Regards, > Kiran > >
