Azure Microsoft.DBforPostgreSQL flexibleServers database collations

Question

I tried to create new database with different (than en_US.utf8) collation on Azure Microsoft.DBforPostgreSQL flexibleServers. CLI and Bicep gives "Internal Server Error" as a message and from Portal I cannot change the collation select input at all? Is it really so that flexibleServers allow only en_US.utf8 collation or is there some properties that I am missing?

Answer 1

I tried creating azure database for PostgreSQL in Azure portal and I have not got the option to select the collation. Even when I tried to create new database, I cannot change character set and collation in portal.

• Therefore, I tried to create a new database using SQL command in Azure Data Studio.

• Connect postgreSQL server in Azure Data Studio. (Refer the MS document on Connect and query PostgreSQL using Azure Data Studio )

• Run this query to list the supported collation and encoding. select * from pg_collation

• Once connected, enter the following SQL script to create a new database with one of the supported collations from the above query.

CREATE  DATABASE  testdb  ENCODING='UTF8' LC_COLLATE='aa_DJ.utf8' TEMPLATE='template0';

• To check the collation of newly created Database, use the following query.

select datname,datcollate from pg_database;