1

Replace with single quote to double single quote not working properly in PostgreSQL 12, it was working fine in PostgreSQL 11.

PostgreSQL 12

Query: SELECT REPLACE(patient.note,'''',''''''), * FROM patient

Output Text: Medicare Secondary Veteran�s Administration

PostgreSQL 11

Query: SELECT REPLACE(patient.note,'''',''''''), * FROM patient

Output Text: Medicare Secondary Veteran’s Administration

let me know if you have any solutions.

Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
  • Show us for both: PostgreSQL 11 and PostgreSQL 12 what do you get when you do this: `select note from patient` ? Thanks! – VBoka Nov 19 '19 at 07:37
  • It is definitely not a single quote. And I doubt if it was ever replaced. – Salman A Nov 19 '19 at 07:37
  • 3
    It seems your migration to Postgres 12 changed the quote (e.g. used a wrong encoding) - and the output for Postgres 11 doesn't match your code. The output shows a `’` whereas your code uses only "straight" quotes: `'` plus in the output for Postgres 11 (or 12) I would expect **two** single quotes, e.g. `''` - so even the second output does not seem correct. –  Nov 19 '19 at 07:37

1 Answers1

3

This has nothing to do with your replacement, because the character in question is not an apostrophe ' (U+0027), but a “right single quotation mark” character ’ (U+2019).

Probably the client encoding for your connection to PostgreSQL v12 isn't set correctly, so that the character is translated to something undesirable. There may also have been a mistake in transferring the character to v12.

To diagnose this, try

SELECT note::bytea FROM patient;

If this contains e28099, the data in your database are fine, and the problem is your client encoding. This assumes that the server encoding of the databases is UTF8.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263