PostgreSQL - Error: invalid byte sequence for encoding "UTF8": 0x00

Question

Currently, we are facing an issue while inserting a record in PostgresSQL. My data-type is of type TEXT. It throws an error saying Error: invalid byte sequence for encoding "UTF8": 0x00.

The data that we are trying to insert contains RTF text which contains text, image followed by again text. We also made sure there are no null values passed or inserted.

We are using **PostgresSQL **version 9.6 and 12 with an encoding set as UTF-8.

Any help would be appreciated.

The RTF data with text and images (contains special characters) should insert into PostgresSQL without any issues. Also, the data type should be of type TEXT.

are you trying to save binary data in a text field? probably you need to encode it with something like uuencode. — Frank N Stein
– Frank N Stein, Commented Mar 24, 2022 at 13:28
We also made sure there are no null values passed or inserted. You clearly didn't do a good job when you made sure. What do you think 0x00 is other than a null value? An image is not text, it's binary, and you can't store it in a text field in any database. — Ken White
– Ken White, Commented Mar 28, 2022 at 16:11
Does this answer your question? Postgres error on insert - ERROR: invalid byte sequence for encoding "UTF8": 0x00 — Bergi
– Bergi, Commented Feb 21, 2024 at 14:56

Laurenz Albe · Accepted Answer · 2022-03-24 13:43:00Z

1

You cannot store a zero byte as part of a text string in PostgreSQL. You have two options:

remove this character from the input string if it is not required
use data type bytea, which is suitable for binary data

If you want to stick with text, you should also figure out what the encoding of the file is.

answered Mar 24, 2022 at 13:43

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2022-03-24 14:32:27Z

0

If you must use text type, you could save it base64 encoded.

There is a space overhead in the DB of about 30% over the raw data and a modest amount of CPU in your app code to encode/decode it.

answered Mar 24, 2022 at 14:32

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Comments

geert3 · Accepted Answer · 2022-07-28 08:23:49Z

0

You can use the decode function to insert binary values, including hex 0, into a text column:

postgres=# create table jos(foo text);
CREATE TABLE
postgres=# insert into jos values('foo'||decode('00', 'hex')||'bar');
INSERT 0 1
postgres=# select * from jos;
     foo
------------------
 \x666f6f00626172
(1 row)

answered Jul 28, 2022 at 8:23

geert3

7,3671 gold badge36 silver badges54 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Error: invalid byte sequence for encoding "UTF8": 0x00

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related