BRUSSER Michael wrote:
>>> Is there a way to find the records with the text field containing
Unicode bytes "0xedbebf"?
>>> Unfortunately this is a very old version 7.3.10
>>
>> This should work on 7.3 (according to the documentation):
>> SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN
val::bytea) = 1;
> Albe, thanks for pointing this out!
> I made a minor change, added decode since text cannot be cast to bytea
and tried something like this:
> SELECT id FROM myTable WHERE position('\360\235\204\236'::bytea IN
decode(myTextField, 'escape')) !=0
> ERROR: decode: Bad input string for type bytea
Hrm. I didn't know that there was no cast from text to bytea in 7.3.
> Maybe this explains why?
> testdb=# select decode('\360\235\204\236'::text, 'escape');
> ERROR: Unicode >= 0x10000 is not supported
No, that is an error on my side. I gave you the wrong byte sequence.
For "0xedbebf" you should actually write '\355\276\277'. But that's no
valid UTF-8 sequence.
> but I'm not ready to give up yet...
If you know the byte sequence that causes trouble, you could also use
something like "sed"
to search and replace it in the dump file.
Or (if there are not too many) you could search for the pattern and
identify the rows
in the database. Then you know which database rows to update.
Yours,
Laurenz Albe