I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table. This field contains a lot of garbage characters (higher than Ascii 128) which Postgres has a difficult time rendering and results in ‘empty looking’ records.

I say empty looking as I am able to query those missing records out of the foreign table using the regexp_replace function to substitute spaces for those problematic characters.

This leaves me with two questions: 1) The Postgres database encoding is UTF-8, so shouldn’t that be able to render any characters properly?

2) Since I can query it, the Postgres db is obviously processing this data at some level. Why does it display that entire field as blank until the special characters are replaced?

  • 1
    Postgres does not render anything; your client software does.
    – mustaccio
    May 29 '19 at 15:47
  • mustaccio is right. Postgres is a server program that does not even have UI, so it can't "render" anything. It's the SQL client application that you use that does the "rendering". So which SQL client are you using? May 29 '19 at 16:41
  • Sorry, I was guilty of conflating PgAdmin and Postgres. It seems to occur with any client I have. PgAdmin, FME, ArcGisPro and our web GIS software all seem to be having some problem that points back to inability to display those characters. So far, they don't seem to have any difficulty if I connect to the original SQL Server db.
    – BigClyde
    May 29 '19 at 19:52
  • @BigClyde: Please fix your question accordingly. Sounds like exotic UNICODE characters or an encoding mismatch. Provide exact version of pgAdmin and of the SQL server DB, its encoding, your OS and some example characters (copy / paste should do it). This related answer might be of help: stackoverflow.com/a/22701212/939860 May 29 '19 at 21:53

The ultimate answer turned out to be different encoding in the two databases. Thanks Erwin for steering me in that direction.

My apologies for not asking the question correctly. I am definitely out of my depth in this area.

  • Please accept your answer so that your question is marked as resolved Mar 22 '20 at 10:22

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.