How to get to the correct position of a syntax error in postgresql

Sometimes position of a syntax returned by PostgreSQL is not correct. You try to go to the specified position in a query (in any text editor) and it shows something totally unrelated. Probably this is due to comments or new lines or something else. And with large queries it can be frustrating to look through it all for some really simple mistake.

In order to get the correct position you will need to execute the query in PostgreSQL's own pgAdmin III tool. The position number will still be incorrectly reported but the cursor will be automatically moved to the position of an error cause. Handy!

Concatenate full name in reverse order with comma and optional title

Sometimes it is preferrable to output full name as a single field/column like
<last_name>, <title> <first_name>
where title and first name are both optional (as occurs in the real world). Because of the different combinations where title and/or first name are null there should be some conditional logic with concatenation. But you can do correct concatenation in a single expression using the fact that coalesce(right(title,0),', ') will return empty string when title is not null and  separator', ' otherwise:

select 
trim(
upper(family_name) || coalesce(', ' || title || ' ', '') || coalesce(coalesce(right(title,0),', ') || given_names, '')
) as person_name 
from person

ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";

I've encountered this error "ERROR: character with byte sequence <...> in encoding "UTF8" has no equivalent in encoding "WIN1252";" while using MySQL Workbench to migrate data from PostgreSQL to MySQL. I was not sure why this was happening because the target MySQL database had UTF8 encoding and I thought that everything can be mapped to it.

The real root cause of this problem turned out to be the PostgreSQL driver I used to connect to the source DB and specified in the Workbench which was PostgreSQL ODBC Driver (ANSI) and after I changed it to PostgreSQL ODBC Driver (UNICODE) everything worked fine.