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.
Advertisement
Answer
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.