UTF8 Postgresql Create Database Like MySQL (including character set, encoding, and lc_type)

postgresql encoding character-encoding collation ddl
Wil Moore III picture Wil Moore III · Apr 1, 2012 · Viewed 33.5k times · Source

For the following MySQL create database statement, what would be the equivalent in postgresql?:

CREATE DATABASE IF NOT EXISTS `scratch` 
  DEFAULT CHARACTER SET = utf8
  DEFAULT COLLATE = utf8_unicode_ci;

I currently have:

CREATE DATABASE "scratch"
  WITH OWNER "postgres"
  ENCODING 'UTF8'
  TABLESPACE "pg_default";

Is that enough or should I be more specific including LOCALE as well?

Answer

michael.bochkaryov picture michael.bochkaryov · Apr 1, 2012

Yes, you can be more specific.

For example:

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

Also I recommend to read the following pages about locales and collations in PostgreSQL:

Related questions

Character with encoding UTF8 has no equivalent in WIN1252

I am getting the following exception: Caused by: org.postgresql.util.PSQLException: ERROR: character 0xefbfbd of encoding "UTF8" has no equivalent in "WIN1252" Is there a way to eradicate such characters, either via SQL or programmatically? (SQL solution should be …

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

While trying to install opennms : /usr/share/opennms/bin/install -l /usr/local/lib -dis I get the error: ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1. and I'm not …

Read More
Postgres issue encoding "UTF8" has no equivalent in encoding "LATIN1"

Our postgres production database server has a database called crd_production which is born out of the template1 template database. Incidentally, on an Ubuntu 12.04 box, the default encoding of the template1 and template0 databases on initial creation of the pgcluster …

Read More