MySQL to PostgreSQL table create conversion - charset and collation

Question

I want to migrate from MySQL to PostgreSQL.My query for create table is like this.

CREATE TABLE IF NOT EXISTS conftype 
(
  CType char(1) NOT NULL,
  RegEx varchar(300) default NULL,  
  ErrStr varchar(300) default NULL,
  Min integer default NULL,
  Max integer default NULL, 
  PRIMARY KEY (CType)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

What is the converted form of this query. I am confused with DEFAULT CHARSET=latin1 COLLATE=latin1_bin part. How can I convert this part?

Answer 1

That one would mean that the table uses only latin-1 (iso-8859-1) character set and latin-1 binary sorting order. In PostgreSQL the character set is database-wide, there is no option to set it on table level.

You could create a mostly compatible database with:

 CREATE DATABASE databasenamegoeshere WITH ENCODING 'LATIN1' LC_COLLATE='C'
     LC_CTYPE='C' TEMPLATE=template0;

However, I personally would consider a MySQL->PostgreSQL port also worthy of switching to UTF-8/Unicode.