Create a Postgres database with a collation other than en_us.utf8

[Pedro Castro Silva]

Hi,

I can't find a way to create databases with a pt_PT.utf8 collation, although it's listed in the available collations. Can anybody please help?

Thank you

Pedro

[Harmit Rishi (Cloud Platform Support)]

Hello, 

Thank you for using Google Groups!

Would you be able to provide the documentation that indicates pt_PT.utf8 is available? Based on my research I am unable to find it on the collation support nor the character set support on PostgreSQL documentation. However, if you are certain it is supported, you can always create a database using gcloud. Your command would consist of the following flags below: 

gcloud sql databases create [DATABASE_NAME] --instance=[INSTANCE_NAME] [--charset=CHARSET] [--collation=COLLATION]

Additionally, you may find further details of how to do this here. 

[Pedro Castro Silva]

Hi Harmit,

Thank you for your help!

The pt_pt.utf8 collation is supported. You can see it on the collations list of pg_catalog on any PostgreSQL database on Cloud SQL.

As for the command you suggested, I've tried it but got an error I can't solve.

gcloud sql databases create testdb --instance=MYINSTANCE --charset=UTF8 --collation=pt_PT.utf8
ERROR: (gcloud.sql.databases.create) HTTPError 400: Invalid request: Failed to create database testdb. Detail: pq: new collation (pt_PT.utf8) is incompatible with the collation of the template database (en_US.UTF8)

The problem is that I can't change (or replace) the template1 database as the Cloud SQL postgres user is not SUPERUSER as it usually is on conventional deployments.

Thank you again!

[Harmit Rishi (Cloud Platform Support)]

Hello Pedro, 

After some trial and error, I was able to get to intended behavior by preforming the query within the instance by SSH. Here is how I was able to do it: 

1. Google Cloud Platform Console > SQL

2. Create PostgreSQL instance 

3. Open instance details > Connect to this instance : Connect using Cloud Shell

4. Enter instance credentials

5. Run: CREATE DATABASE "example_db" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'pt_PT.UTF-8' LC_CTYPE = 'pt_PT.UTF-8' TEMPLATE template0;
6.  pt_PT.utf8 will be created and you will be able to see it in GCP console> SQL: Databases 

If you would like more details about the query provided, you may refer to the following StackOverflow discussion here. As for the SUPERUSER attributes, you may see the following documentation that discusses the cloudsqlsuperuser role as SUPERUSER is not available within Cloud SQL due to it being Google-managed.

I hope this helps!

[Pedro Castro Silva]

Totally awesome solution!!!

Thank you Harmit! If you happen to travel to Porto, Portugal free beer awaits you