2

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?
clamentjohn
  • 3,417
  • 2
  • 18
  • 42

1 Answers1

0

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".

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • You got the case wrong (`utf` is lower case), and there is no hyphen in the name. See my updated answer. – Laurenz Albe Apr 21 '21 at 07:22
  • The command `locale -a` gives you the list of all supported locales in your system. – Giacomo Catenazzi Apr 21 '21 at 07:38
  • Unlikely. Please show the exact command string. – Laurenz Albe Apr 21 '21 at 07:41
  • @GiacomoCatenazzi I'm connected to a AWS Lightsail postgres instance. Unlike RDS I don't have fine grained access to its features – clamentjohn Apr 21 '21 at 07:41
  • @LaurenzAlbe Here is the whole command string `CREATE DATABASE __edgedbtpl__ OWNER='edgedb' IS_TEMPLATE = TRUE TEMPLATE='template0' ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE="C.utf8";` – clamentjohn Apr 21 '21 at 07:42
  • `LC_COLLATE` and `LC_CTYPE` have to be the same and match the encoding. – Laurenz Albe Apr 21 '21 at 08:48
  • @LaurenzAlbe Meaning `LC_COLLATE` and `LC_CTYPE` must be "C.utf8". I tried that too. But I still get the error: invalid local name: "C.utf8". Is it possible that C.utf8 is indeed unavailable? But my question is why did pg_collation have C.utf8. And why did SHOW LC_CTYPE only respond with en_US.UTF-8 – clamentjohn Apr 21 '21 at 08:51
  • 1
    `SHOW` just shows the current value of a configuration parameter. I don't know why you get that error. Probably Amazon weirdness. Ask them, since you cannot get at the operating system. – Laurenz Albe Apr 21 '21 at 09:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231408/discussion-between-clmno-and-laurenz-albe). – clamentjohn Apr 21 '21 at 09:06