I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as:


I want to be able to dig out these odd characters and replace them with simple ' or " alternatives. However, I'm having a bit of a job getting either ~ or LIKE to be able to pull the correct rows in, let alone be able to replace them.

The database is UTF8 and the client_encoding is also set to UTF8. I've tried selecting \u string and \x strings, but can't seem to get anything reliable. For example, all of the following queries return 0 rows or ERROR: invalid byte sequence for encoding "UTF8": 0x80

select col from tab where col like '%\u0080\u0099%';
select col from tab where col ~'\u0080\u0099';
select col from tab where col like '%\x8099%';
select col from tab where col ~'\x8099';

Any ideas?


If what you see is donâ\u0080\u0099t then those are literal backslashes in your table, not escape sequences.

Try to double the backslashes to get literal ones:

select col from tab where col ~~ E'%\\u0080%';

And use the proper E'' syntax for PostgreSQL 8.4 with that.

  • Brilliant! This works on the \u0080 characters, I just need to replace the odd characters before that now. psql is printing those on resultsets as It<C3><A2>s, so I'm assuming I'm just looking for unicode chars c3 and a2? Google tells me these characters equate to â, but I'm not sure what escape sequence I need to use to find this - \uc3a2? – growse Feb 29 '12 at 16:16
  • @growse: It's hard to tell what should be there. It very much depends on how the text was mangled, and there is a myriad of possible ways. Some detective work may be needed. For instance, your example donâ\u0080\u0099t looks very much like it should be don't. A case for replace (col, E'â\\u0080\\u0099',''''). Be sure to view the results in a SELECT statement before applying with UPDATE. – Erwin Brandstetter Feb 29 '12 at 21:58
  • Also, if you want to insert the character â, you can just insert it as is - you are using UTF-8 as server and client encoding. – Erwin Brandstetter Feb 29 '12 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.