On Thu, Apr 29, 2010 at 05:02, Andreas <maps.on@xxxxxxx> 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/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
