PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Client Encoding in psycopg3 missing, yet gives error

So I see in psycopg 2, there is a set_client_encoding('UTF8') function. In the psycopg 3 documentation it reads:

client_encoding is gone

Here I create the connection:

conn = psycopg.connect(conninfo, row_factory = dict_row, autocommit = autocommit, client_encoding="UTF8")

But then when I go to actually use the connection:

cur.execute(query, params)

I get an encoding error.

UnicodeEncodeError: 'charmap' codec can't encode character 'u202f' in position 15: character maps to <undefined> encoding with

‘cp1252’ codec failed

Using the l in postgres, all databases are shown as WIN1252. This is on Windows 10. The version of PostgreSQL 16.3. I am working with code which was developed on Linux and I am trying to get the same functionality working on Windows. I really would like to work in UTF-8.

How would I work around this in pyscopyg 3?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @klin sort of gave the right answer to the question being asked, and I always do like users who answer the question instead of whatever question the commenter/answerer wants to answer.

    That being said, the best answer in this case, or the answer that applied to my case was to drop the database and recreate it with the required encoding and collation. This is acceptable to me because I can quickly repopulate all the data once a new table is created with a new encoding. One should do a dump before dropping if the data needs to be kept around for the new database.

    So this create statement which solves the problem for me is as follows:

    CREATE DATABASE "mydbname"
WITH OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

  2. 0

    You should check what encoding is detected by psycopg, e.g.:

    import psycopg as pg

conn = pg.connect('dbname=test user=my_user password=my_password')
print(conn.info.encoding)

    You can define the parameter in conninfo (dns):

    conn = pg.connect('dbname=test user=my_user password=my_password client_encoding=utf8')
print(conn.info.encoding)

    or with the Postgres SET command:

    conn = pg.connect('dbname=test user=my_user password=my_password')
with conn.cursor() as cursor:
    cursor.execute('set client_encoding to utf8')
print(conn.info.encoding)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search