PostgreSQL: create database with UTF8 encoding same as in MySQL (including character set, encoding, and lc_type)

Question

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 1

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:

• http://www.postgresql.org/docs/current/interactive/locale.html
• http://www.postgresql.org/docs/current/interactive/collation.html