On 28/03/13 16:59, Don Parris wrote: > If I created the database using the UTF-8 encoding, then why would some > data be encoded differently than the rest? And how can I control how > the data gets inserted? See my previous post, where I mentioned loading > a good chunk of the data via the \copy command in psql, and then later > added more via PGAdmin. Many records seem to work just fine, but quite > a few others don't - and I was just naively entering or loading data > without knowing any encoding was being changed.
If the database is created with UTF-8 encoding, all character data will be encoded as UTF-8. The problem was that your client was using SQL_ASCII encoding so any UTF-8, non-ASCII data (i.e., characters above decimal 127) received from PG couldn't be decoded. IIRC the client encoding is set according to the template0 encoding. I would do a psql -l to see the encoding of other databases in your cluster, in particular template0, template1 and postgres. Joe
As per my previous post, I had difficulty trying to change the character encoding, even though I logged in as superuser. So I dropped the test DB and re-created it, re-installed my ltree extension and then finished re-creating the tables and re-loading the data via \copy.
Finally, I ran my Python3 script against the data without setting the client encoding and all works as expected. The upshot is I have a slightly better understanding of the problem. I greatly appreciate everyone helping out!