Re: COPY fails on 8.1 with invalid byte sequences in text

On Fri, 2006-10-27 at 14:42 -0700, Jeff Davis wrote:
> It seems to be essentially a data corruption issue if applications
> insert binary data in text fields using escape sequences. Shouldn't
> PostgreSQL reject an invalid UTF8 sequence in any text type?
>

Another note: PostgreSQL rejects invalid UTF8 sequences in other
contexts. For instance, if you use PQexecParams() and insert using type
text and any format (text or binary), it will reject invalid sequences.
It will of course allow anything to be sent when the type is bytea.

Also, I thought I'd publish the workaround that I'm using.

I created a function that seems to work for validating text data as
being valid UTF8.

CREATE OR REPLACE FUNCTION valid_utf8(TEXT) returns BOOLEAN
LANGUAGE plperlu AS
$valid_utf8$
use utf8;
return utf8::decode($_[0]) ? 1 : 0;
$valid_utf8$;

I just add a check constraint on all of my text attributes in all of my
tables. Not fun, but it works.

Regards,
    Jeff Davis