UTF8 Postgresql Create Database Like 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