Re: Need magic that checks if a collumn contains values that CAN NOT be converted to Latin1

On Thu, Apr 29, 2010 at 05:02, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
>
> I've got an 8.4.3 Unicode DB that accidentally holds a few records with
> characters that can't be converted to Latin1 or 9 for output to CSV.
>
> I'd just need a way to check if a collumn contains values that CAN NOT be
> converted from Utf8 to Latin1 to select all those affected records.
>
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR:  character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«
> Regrettably it doesn't explain where it found this sign.
>
> Select '\xe28093'
> complains that this weren't a valid UTF8 code at all.
> So how was it accepted and stored in the first place?
>
> When I know which record has faulty content I can correct it.

Wrap your check in a simple function:

CREATE OR REPLACE FUNCTION public.is_valid_encoding(vtext text, encname text)
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
BEGIN
PERFORM convert_to(vtext, encname);
EXCEPTION WHEN untranslatable_character THEN
RETURN 'f';
END;
RETURN 't';
END;
$$

And execute
SELECT * FROM my_table WHERE NOT is_valid_encoding(my_column, 'LATIN1')

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/