Re: finding rows with invalid characters

Hey Sim,

Maybe this helps:
http://blog.tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html

2010/11/21 Sim Zacks <sim(at)compulab(dot)co(dot)il>

> I am using PG 8.2.17 with UTF8 encoding.
> "PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1
> (Gentoo 4.1.1)"
>
> One of my tables somehow has invalid characters in it:
>
>> ERROR: invalid byte sequence for encoding "UTF8": 0xa9
>> HINT: This error can also happen if the byte sequence does not match the
>> encoding expected by the server, which is controlled by "client_encoding".
>>
> I have already manually found a number of the bad rows by running queries
> with text functions (upper) between groups of IDs until I found the specific
> bad row.
>
> 1) Is there a quicker way to get a list of all rows with invalid characters
> 2) Shouldn't the database prevent these rows from being entered in the
> first place?
> 3) I have backups of this database (using -Fc) and I noticed that on
> restore, this table is not restored because of this error. Is there a way to
> fix the existing backups, or tell the restore to ignore bad rows instead of
> erroring out the whole table?
>
> Thanks
> Sim
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.