Cannot set collation PostgreSQL database to C or POSIX

Problem

When creating a new Cloud SQL PostgreSQL database using gcloud or Terraform, and setting a collation (LC_COLLATE or LC_TYPE), you may receive one of the following errors:

"ERROR: new collation (C) is incompatible with the collation of the template database (en_US.UTF8)"
"ERROR: new collation (POSIX) is incompatible with the collation of the template database (en_US.UTF8)"
"Creating Cloud SQL database...failed. Database creation failed. Check if a database named <name> already exists. ERROR: (gcloud.sql.databases.create) INTERNAL_ERROR".

Environment

  • Cloud SQL
  • PostgreSQL

Solution

  1. Create the database using the psql command like the following:
    create database testdb LC_COLLATE='C' LC_CTYPE='C' template template0;
    

Cause

The default template used when creating a new database is configured for UTF-8 collation. This value cannot be overridden.