Invalid local name: C.UTF-8 even though the collname exist in pg_collation.collname

Question

As this might have something to do with AWS Lightsail, I've cross posted this question on AWS - Click Here

I'm trying to create a template database using

CREATE DATABASE __edgedbtpl__  OWNER='edgedb' IS_TEMPLATE = TRUE TEMPLATE='template0' ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C.UTF-8';

But this fails and gives me the error

ERROR:  invalid locale name: "C.UTF-8"

I checked if the PostgreSQL server supports the C.UTF-8 locale, using

SELECT collname FROM pg_collation WHERE lower(replace(collname, '-', '')) = 'c.utf8' LIMIT 1;

which gives me the response

 collname 
----------
 C.utf8
(1 row)

Question

1. How are the collnames in pg_collation different from SHOW LC_CTYPE and SHOW LC_COLLATE?
2. SHOW LC_COLLATE and SHOW LC_TYPE responded with en_US.UTF-8 and not C.UTF-8. So how should I identify if a certain locale is supported?

Answer 1

Collation names are identifiers, not string literals in PostgreSQL. Use double quotes instead of single quotes. Also, case and spelling matter, so use "C.utf8".