Replace with single quote to double single quote not working in PostgreSQL 12

Question

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.

Answer 1

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.