PostgreSQL - Psycopg2 - copy_from - Invalid byte sequence for encoding "UTF8": 0x00

Question

I want to insert bytes into my PostgreSQL (9.5.7) database column with the type bytea, using the Psycopg2 (2.7.1) copy_from() method.

I can insert my bytes with the following code :

psycopg2_cursor.copy_from(
    StringIO("\x30\x40\x50"),
    "my_table",
)

By executing a SELECT into my table after the insertion, I get the expected value from the bytea column:

\x304050

Now, I want to prepend my bytes with the byte 0:

psycopg2_cursor.copy_from(
    StringIO("\x00\x30\x40\x50"),
    "my_table",
)

I get the error : psycopg2.DataError: invalid byte sequence for encoding "UTF-8": 0x00. From my understanding, this error should only be triggered when inserting a null byte into a text field, but should work as expected into a bytea field. Am I missing something ? Is there any simple way to insert a null byte into a bytea column ?

Thanks!

what's your standard_conforming_strings setting?

Vao Tsun
– Vao Tsun

2017-06-12 07:42:06 +00:00
Commented Jun 12, 2017 at 7:42 — Vao Tsun
– Vao Tsun, Commented Jun 12, 2017 at 7:42
did you try StringIO('\x30\x40\x50') instead?..

Vao Tsun
– Vao Tsun

2017-06-12 07:57:50 +00:00
Commented Jun 12, 2017 at 7:57 — Vao Tsun
– Vao Tsun, Commented Jun 12, 2017 at 7:57

Vao Tsun · Accepted Answer · 2017-06-12 10:19:38Z

2

https://www.postgresql.org/docs/current/static/sql-copy.html

the following characters must be preceded by a backslash if they appear as part of a column value: backslash itself, newline, carriage return, and the current delimiter character.

just realized you are using COPY, so you have to escape backslash:

t=# copy b from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \\x00
>> \.
COPY 1
t=# copy b from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \x00
>> \.
ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY b, line 1: "\x00"

this should do the trick:

psycopg2_cursor.copy_from(
    StringIO("\\x00\\x30\\x40\\x50"),
    "my_table",
)

edited Jun 12, 2017 at 10:19

answered Jun 12, 2017 at 7:46

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

6 Comments

jean553 Over a year ago

I added the following parameter to my psycopg2 connection : options="-c standard_conforming_strings=on", but I still get the same error. I tried StringIO('\x30\x40\x50') and it works correctly, but I really need to insert this null byte. I tried to use a BytesIO instead of a StringIO, but I still get the same result.

Vao Tsun Over a year ago

try that? select decode('00203040','hex') - is it producing your wanted result?..

jean553 Over a year ago

Yes, it produces the wanted result when using it in PSQL (INSERT INTO my_table VALUES(decode('001060', 'hex'));), but I need to insert my bytes with the copy_from() method from my python code, not directly with a SQL query.

Vao Tsun Over a year ago

last attempt: psycopg2_cursor.copy_from( StringIO("\\x00\\x30\\x40\\x50"), "my_table", ) ?..

jean553 Over a year ago

Still the same (invalid byte sequence for encoding "UTF8": 0x00). The good news is that I can insert bytes from my python code when using cursor.execute("INSERT INTO my_table VALUES(decode('005566', 'hex'));") but for performances issues (details on my github: github.com/jean553/massive-insert-postgresql-tornado), I would prefer to insert with copy_from().

|

Clodoaldo Neto · Accepted Answer · 2017-06-12 15:54:17Z

1

To insert a binary with copy it is necessary to use the binary format which is not what you want. Use the extras.execute_values method

from psycopg2.extensions import Binary

binaries = [[Binary('\x00\x20')], [Binary('\x00\x30')]]

insert_query = 'insert into t (b) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, binaries, page_size=100
)

edited Jun 12, 2017 at 15:54

answered Jun 12, 2017 at 13:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Psycopg2 - copy_from - Invalid byte sequence for encoding "UTF8": 0x00

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related