Is it possible to store invalid UTF-8 byte sequences in a postgres text column?

Question

The encoding of my postgres database is UTF-8. In a certain table I have a text column into which I would like to insert some data. Now, the data is mostly valid UTF-8, but there are a number of instances of invalid byte sequences which I do not want to remove or substitute. My question is, is there any way of inserting the data into the text column without removing or substituting its invalid byte sequences?

Here's a simple example, executed from the shell (bash) command-line courtesy of psql:

psql main postgres <<<"create table t1 (a text); insert into t1 (a) values (E'a\xC0b');";
## CREATE TABLE
## ERROR:  invalid byte sequence for encoding "UTF8": 0xc0 0x62

I know this is probably a long shot, but is there any way of disabling postgres's validation of inserted text, perhaps on an ad hoc basis? I don't see how it would trouble postgres to have some byte sequences in text column data that happen to not be valid for the database's configured character encoding.

If this is not possible, I guess the only recourse is to store the data as straight binary data using the bytea data type, but please let me know if there's a better solution out there.

Answer 1

If you want to store invalidly encoded data, use bytea. As mu alludes to, you'll have to deal with the fact that substrings and lengths etc are now byte-oriented, not character-oriented.

It is a problem to have invalidly encoded text. How would left(n) know how many characters to grab? How would indexing determine a correct lexical sort order? etc. Not to mention that PostgreSQL can't do on the fly character encoding conversion (e.g. client_encoding = 'latin-1') if you have badly encoded data in a table.

You seem to want some kind of lax or forgiving mode for encodings, where it falls back to byte-based interpretation if the data isn't valid in the current encoding, or replaces it with ? or something. That's a valid thing to want, but is not supported by PostgreSQL.