Pg::Error: Error: New Encoding (Utf8) Is Incompatible

PG::Error: ERROR: new encoding (UTF8) is incompatible

Ok, below steps resolved the problem:

1. First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:

   UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

2. Now we can drop it:

   DROP DATABASE template1;

3. Now its time to create database from template0, with a new default encoding:

   CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

4. Now modify template1 so it’s actually a template:

   UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

5. Now switch to template1 and VACUUM FREEZE the template:

   \c template1

   VACUUM FREEZE;

Problem should be resolved.

rake db:create encoding error with postgresql

The main problem here is that your template database (template1) has been created with an ASCII encoding and you're telling PostgreSQL to create the new database with UTF8 encoding. Needless to say, it's not particularly pleased about that. What you can do is erase your template1 database and re-create it using these instructions. This can also be a problem when your hosting provider hasn't properly set the locale. You can read more about fixing your missing locales.

I found all of this info through this post about Fixing PostgreSQL's default encoding on Ubuntu 9.10

How to change the template database collection coding

From PostgreSQL documentation:

Another common reason for copying template0 instead of template1 is
that new encoding and locale settings can be specified when copying
template0, whereas a copy of template1 must use the same settings it
does. This is because template1 might contain encoding-specific or
locale-specific data, while template0 is known not to.

You can use only template0 to create new database with different encoding and locale:

CREATE DATABASE newdb
WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'zh_CN.UTF-8'
   CONNECTION LIMIT = -1
   TEMPLATE template0;

This will work, however it means that any changes you made to template1 won't be applied to newly created database.

To change encoding and collation of template1 you have to first delete template1 and then create new template template1 from template0. How to drop template database is described here. Then you can create new database template1 with chosen encoding/collation and mark it as a template by setting datistemplate=true (example):

update pg_database set datistemplate=true  where datname='template1';

encoding UTF8 does not match locale en_US; the chosen LC_CTYPE setting requires encoding LATIN1

Thanks for locale output. OpenNMS seems to be using your en_US (non-UTF-8) locale in order to create postgres db, and this is wrong. This should work:

export LANG=en_US.UTF-8
locale   # confirm that it shows only en_US.UTF-8 for all settings
# finally, run your opennms installer
/usr/share/opennms/bin/install -l /usr/local/lib -dis

Postgres issue encoding UTF8 has no equivalent in encoding LATIN1

As guessed, the problem was with the client_encoding on the database.

crd_production=# show client_encoding;
 client_encoding 
-----------------
 LATIN1
(1 row)

To change the client encoding to UTF-8, you need to do this

crd_production=#  SET client_encoding = 'UTF8';
SET

Check again

crd_production=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

Things work fine now.

Character with encoding UTF8 has no equivalent in WIN1252

What do you do when you get this message? Do you import a file to Postgres? As devstuff said it is a BOM character. This is a character Windows writes as first to a text file, when it is saved in UTF8 encoding - it is invisible, 0-width character, so you'll not see it when opening it in a text editor.

Try to open this file in for example Notepad, save-as it in ANSI encoding and add (or replace similar) set client_encoding to 'WIN1252' line in your file.

Ruby 2.2: PG::CharacterNotInRepertoire: ERROR: invalid byte sequence for encoding UTF8

Looks like this is a known issue with "pg" gem: https://bitbucket.org/ged/ruby-pg/issue/197/ruby-220-byte-encoding-issue

It should be fixed in 0.18 pre-release

Postgres error on insert - ERROR: invalid byte sequence for encoding UTF8: 0x00

PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

Source: http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

---

Related Topics