postgres encode problem

Question

I have a Postgres 8.2 database.
The table encode : SQL-ASCII. (I can't change this :( )
Xhtml charset: charset=iso-8859-2.
The files encode is ANSII. (notepad++)

When I show retrieved data with AJAX (jQuery) I must use utf8_encode(). When this data, insert into database, instead of accent letters (öüőűú) I see this &# 245; &# 251; and others.

Question: How can I solve this encode problem?

EDIT This work me:

pg_dump -E LATIN9 alt > alt.sql
createdb -T template0 -E UTF8 new_database
psql -f alt.sql new_database

Denis de Bernardy · Accepted Answer · 2011-07-24 10:26:13Z

1

I sounds like you're mixing utf8 encoding and html/xml escaping.

To reconfigure the database's encoding setting, the relevant bits say:

The default character set is selected while initializing your PostgreSQL database cluster using initdb. It can be overridden when you create a database, so you can have multiple databases each with a different character set.

As well as:

CREATE DATABASE name
   [ [ WITH ] [ OWNER [=] dbowner ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ TABLESPACE [=] tablespace ]
       [ CONNECTION LIMIT [=] connlimit ] ]

Edit: per horse's name, you cannot alter the database's encoding, so you may need to dump the schema and data, edit the dump accordingly, and re-create it accordingly.

edited Jul 24, 2011 at 10:26

answered Jul 24, 2011 at 9:26

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

You can only specify the encoding during creation of the database. You cannot change it once the database has been created. See the ALTER DATABASE command: postgresql.org/docs/current/static/sql-alterdatabase.html

EncodeProblem Over a year ago

Unfortunately, I can't drop data(base), It's a company's database.

EncodeProblem Over a year ago

Sorry, my English knowledge is not perfect, but now, I understand what you advised. :)

Collectives™ on Stack Overflow

postgres encode problem

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related