postgresql collate and ctype

postgresql-collate-ctype.sh

  #!/bin/bash
  
  # `myDB` is your database
  # `OWNER = "myself"` is your role (default is your username, or check psql \du)
  
  # tip: when Postgres is installed, it automatically creates a database user that matches your username, 
  # so that you can get started right away.
  

  locale-gen tr_TR.UTF-8
  locale -a

  # dump into file
  pg_dump myDB > /tmp/myDB.sql

  # create an empty db with the right encoding (on older versions the escaped single quotes are needed!)
  psql -c 'CREATE DATABASE "tempDB" WITH OWNER = "myself" LC_COLLATE = '\''tr_TR.UTF-8'\'' LC_CTYPE = '\''tr_TR.UTF-8'\''  TEMPLATE template0;'

  # import in the new DB
  psql -d tempDB -1 -f /tmp/myDB.sql

  # rename databases
  psql -c 'ALTER DATABASE "myDB" RENAME TO "myDB-wrong-encoding";'
  psql -c 'ALTER DATABASE "tempDB" RENAME TO "myDB";'

  # see the result
  psql myDB -c "SHOW LC_COLLATE"