Is there a way to turn off encoding checking for a text column during insertion?

[ns-gzhang]

It seems to me that there is encoding checking during argument bind-in to a parameter corresponding to a text column.
For a statement:

var insertQuery = `insert into files values(10065, 10000, '2023-01-01 12:12:12.123', $1) on conflict do nothing`
    ct, err := conn.Exec(context.Background(), insertQuery, []byte{0x9d, 1, 0xe5, 0x61, 0x38})

when $1 corresponds to a text column, it will issue an error:

ERROR: invalid byte sequence for encoding "UTF8": 0x9d (SQLSTATE 22021)

If I add type cast as follows:

var insertQuery = `insert into files values(10065, 10000, '2023-01-01 12:12:12.123', $1::bytea) on conflict do nothing`

Insertion would work fine. And if I put a bytea literal '\x9d01e56138' in the statement in place of the parameter $1, it would work also. That seems to indicate the argument bind-in process was checking the encoding value.

Is there a way to set the behavior to not check the encoding? My scenario is for text[] columns. I can create bytea[] columns, but Debezium CDC does not support bytea[] at the moment, but it does support text[]. And I need to insert arbitrary binary values into these (text[]) columns (encrypted values). Maybe type cast like $1::bytea[] for text[] would help? Thanks!

[jackc]

If I understand correctly your 4th column in the files table is text? If so, it's not a the parameter binding that's the issue, it's that PostgreSQL literally will not store invalid UTF8 in a UTF8 collated text column.

I would also suggest double checking that the bytea literal is doing what you expect. It might actually be storing that text.

[ns-gzhang]

Thanks Jack.
That's correct. The 4th column is text.

I struggled a bit to determine if this is a server checking or parameter binding checking. Seems to me it's binding checking - if it's not done by the client, then it's by the server for the binding. Seems $1:bytea or $1:bytea[] does provide a workaround.

Here is my experiment using psql for the server behavior:

insert into files values(20000, 10000, '2023-01-01 12:12:12.123', '\x9d01e56138');
INSERT 0 1
insert into files values(20001, 10000, '2023-01-01 12:12:12.123', '\x9d01e56138'::bytea);
INSERT 0 1
insert into files values(20002, 10000, '2023-01-01 12:12:12.123', 'abc');
INSERT 0 1

check what's inserted:

select encode(appname::bytea, 'hex'), appname, id from files where id >= 20000;
   encode   |   appname    | id 
------------+--------------+--------------
 9d01e56138 | \x9d01e56138 |        20000
 9d01e56138 | \x9d01e56138 |        20001
 616263     | abc          |        20002
(3 rows)

So the server didn't validate encoded values in a text column, and both inserts inserted binary values with invalid UTF8 literal, and the server was able to detect invalid binary and output as hex. Do you see any problem in my testing? Thanks.

[jackc]

The SQLSTATE 22021 is definitely server side.

I suspect what is happening in the cases that appear to be working is the binary data is encoded into hex and the hex is saved into the text column.

The visual output from psql does not make this easy to distinguish. For example:

jack@[local]:5432 jack=# select '\x00'::bytea, length('\x00'::bytea), '\x00'::bytea::text, length('\x00'::bytea::text);
 bytea │ length │ text │ length
───────┼────────┼──────┼────────
 \x00  │      1 │ \x00 │      4

Note how the NUL character is 1 byte in bytea and 4 in text.

That's a reasonable approach if you don't actually have bytea available. But you need to be aware of what is happening.

[ns-gzhang]

Thanks Jack. Appreciate your testcase. So ::bytea and ::text are actually doing type conversion, not re-interpretation cast. Now I confirmed that by using length() function. I may need to do conversion myself to base64 with a bit better storage efficiency...