v10-0003-CREATE-DATABASE-make-LOCALE-apply-to-all-collati.patch

From 7ee9bfc7ded5906f5b27390061513bbab3d87555 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Tue, 25 Apr 2023 15:01:55 -0700
Subject: [PATCH v10 3/4] CREATE DATABASE: make LOCALE apply to all collation
 providers.

Previously, LOCALE applied only to LC_COLLATE and LC_CTYPE. That could
lead to confusion when the provider is implicit, such as when it is
inherited from the template database, or when ICU was made default at
initdb time in commit 27b62377b4.

Reverts incomplete fix 5cd1a5af4d.

Discussion: https://postgr.es/m/3391932.1682107209@sss.pgh.pa.us
---
 doc/src/sgml/ref/create_collation.sgml        | 23 +++++---
 doc/src/sgml/ref/create_database.sgml         | 57 +++++++++++++++----
 doc/src/sgml/ref/createdb.sgml                |  5 +-
 doc/src/sgml/ref/initdb.sgml                  |  7 ++-
 src/backend/commands/collationcmds.c          |  2 +-
 src/backend/commands/dbcommands.c             | 51 +++++++++++++++--
 src/bin/initdb/initdb.c                       | 27 +++++++--
 src/bin/initdb/t/001_initdb.pl                | 28 +++++----
 src/bin/pg_dump/pg_dump.c                     | 55 ++++++++++++++----
 src/bin/scripts/createdb.c                    | 13 ++---
 src/bin/scripts/t/020_createdb.pl             | 28 +++++++--
 src/test/icu/t/010_database.pl                | 22 ++++---
 .../regress/expected/collate.icu.utf8.out     | 22 +++----
 13 files changed, 251 insertions(+), 89 deletions(-)

diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml
index 67ea247b39..48b62f77cb 100644
--- a/doc/src/sgml/ref/create_collation.sgml
+++ b/doc/src/sgml/ref/create_collation.sgml
@@ -85,9 +85,16 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
 
      <listitem>
       <para>
-       This is a shortcut for setting <symbol>LC_COLLATE</symbol>
-       and <symbol>LC_CTYPE</symbol> at once.  If you specify this,
-       you cannot specify either of those parameters.
+       The locale name for this collation. See <xref
+       linkend="collation-managing-create-libc"/> and <xref
+       linkend="collation-managing-create-icu"/> for details.
+      </para>
+      <para>
+       If <replaceable>provider</replaceable> is <literal>libc</literal>, this
+       is a shortcut for setting <symbol>LC_COLLATE</symbol> and
+       <symbol>LC_CTYPE</symbol> at once.  If you specify
+       <replaceable>locale</replaceable>, you cannot specify either of those
+       parameters.
       </para>
       <para>
        If <replaceable>provider</replaceable> is <literal>builtin</literal>,
@@ -102,8 +109,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
 
      <listitem>
       <para>
-       Use the specified operating system locale for
-       the <symbol>LC_COLLATE</symbol> locale category.
+       If <replaceable>provider</replaceable> is <literal>libc</literal>, use
+       the specified operating system locale for the
+       <symbol>LC_COLLATE</symbol> locale category.
       </para>
      </listitem>
     </varlistentry>
@@ -113,8 +121,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
 
      <listitem>
       <para>
-       Use the specified operating system locale for
-       the <symbol>LC_CTYPE</symbol> locale category.
+       If <replaceable>provider</replaceable> is <literal>libc</literal>, use
+       the specified operating system locale for the <symbol>LC_CTYPE</symbol>
+       locale category.
       </para>
      </listitem>
     </varlistentry>
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index 17550c19bb..9f20c93267 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -145,8 +145,22 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
       <term><replaceable class="parameter">locale</replaceable></term>
       <listitem>
        <para>
-        This is a shortcut for setting <symbol>LC_COLLATE</symbol>
-        and <symbol>LC_CTYPE</symbol> at once.
+        Sets the default collation order and character classification in the
+        new database.  Collation affects the sort order applied to strings,
+        e.g., in queries with ORDER BY, as well as the order used in indexes
+        on text columns.  Character classification affects the categorization
+        of characters, e.g., lower, - upper and digit.  Also sets the
+        associated aspects of the operating system environment,
+        <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>.  The
+        default is the same setting as the template database.  See <xref
+        linkend="collation-managing-create-libc"/> and <xref
+        linkend="collation-managing-create-icu"/> for details.
+       </para>
+       <para>
+        Can be overridden by setting <xref
+        linkend="create-database-lc-collate"/>, <xref
+        linkend="create-database-lc-ctype"/>, or <xref
+        linkend="create-database-icu-locale"/> individually.
        </para>
        <para>
         If <xref linkend="create-database-locale-provider"/> is
@@ -170,11 +184,17 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
       <term><replaceable class="parameter">lc_collate</replaceable></term>
       <listitem>
        <para>
-        Collation order (<literal>LC_COLLATE</literal>) to use in the new database.
-        This affects the sort order applied to strings, e.g., in queries with
-        ORDER BY, as well as the order used in indexes on text columns.
-        The default is to use the collation order of the template database.
-        See below for additional restrictions.
+        Sets <literal>LC_COLLATE</literal> in the database server's operating
+        system environment.  The default is the setting of <xref
+        linkend="create-database-locale"/> if specified; otherwise the same
+        setting as the template database.  See below for additional
+        restrictions.
+       </para>
+       <para>
+        If <xref linkend="create-database-locale-provider"/> is
+        <literal>libc</literal>, also sets the default collation order to use
+        in the new database, overriding the setting <xref
+        linkend="create-database-locale"/>.
        </para>
       </listitem>
      </varlistentry>
@@ -182,10 +202,17 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
       <term><replaceable class="parameter">lc_ctype</replaceable></term>
       <listitem>
        <para>
-        Character classification (<literal>LC_CTYPE</literal>) to use in the new
-        database. This affects the categorization of characters, e.g., lower,
-        upper and digit. The default is to use the character classification of
-        the template database. See below for additional restrictions.
+        Sets <literal>LC_CTYPE</literal> in the database server's operating
+        system environment.  The default is the setting of <xref
+        linkend="create-database-locale"/> if specified; otherwise the same
+        setting as the template database.  See below for additional
+        restrictions.
+       </para>
+       <para>
+        If <xref linkend="create-database-locale-provider"/> is
+        <literal>libc</literal>, also sets the default character
+        classification to use in the new database, overriding the setting
+        <xref linkend="create-database-locale"/>.
        </para>
       </listitem>
      </varlistentry>
@@ -194,7 +221,13 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
       <term><replaceable class="parameter">icu_locale</replaceable></term>
       <listitem>
        <para>
-        Specifies the ICU locale ID if the ICU locale provider is used.
+        Specifies the ICU locale (see <xref
+        linkend="collation-managing-create-icu"/>) for the database default
+        collation order and character classification, overriding the setting
+        <xref linkend="create-database-locale"/>.  The <xref
+        linkend="create-database-locale-provider"/> must be ICU.  The default
+        is the setting of <xref linkend="create-database-locale"/> if
+        specified; otherwise the same setting as the template database.
        </para>
        <para>
         If specified as <literal>C</literal> or <literal>POSIX</literal>, the
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml
index 5352cce744..94937e1d6d 100644
--- a/doc/src/sgml/ref/createdb.sgml
+++ b/doc/src/sgml/ref/createdb.sgml
@@ -124,7 +124,10 @@ PostgreSQL documentation
       <listitem>
        <para>
         Specifies the locale to be used in this database.  This is equivalent
-        to specifying both <option>--lc-collate</option> and <option>--lc-ctype</option>.
+        to specifying <option>--lc-collate</option>,
+        <option>--lc-ctype</option>, and <option>--icu-locale</option> to the
+        same value. Some locales are only valid for ICU and must be set with
+        <option>--icu-locale</option>.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index d9620e5931..5eabaaec3a 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -116,9 +116,10 @@ PostgreSQL documentation
   <para>
    To choose a different locale for the cluster, use the option
    <option>--locale</option>.  There are also individual options
-   <option>--lc-*</option> (see below) to set values for the individual locale
-   categories.  Note that inconsistent settings for different locale
-   categories can give nonsensical results, so this should be used with care.
+   <option>--lc-*</option> and <option>--icu-locale</option> (see below) to
+   set values for the individual locale categories.  Note that inconsistent
+   settings for different locale categories can give nonsensical results, so
+   this should be used with care.
   </para>
 
   <para>
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index 3b2ba38250..58316ec19e 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -318,7 +318,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
 				if (langtag && strcmp(colliculocale, langtag) != 0)
 				{
 					ereport(NOTICE,
-							(errmsg("using standard form \"%s\" for locale \"%s\"",
+							(errmsg("using standard form \"%s\" for ICU locale \"%s\"",
 									langtag, colliculocale)));
 
 					colliculocale = langtag;
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 5c8caeac2d..3d53e29c7e 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -710,6 +710,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	char	   *dbname = stmt->dbname;
 	char	   *dbowner = NULL;
 	const char *dbtemplate = NULL;
+	char	   *builtin_locale = NULL;
 	char	   *dbcollate = NULL;
 	char	   *dbctype = NULL;
 	char	   *dbiculocale = NULL;
@@ -894,6 +895,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	}
 	if (dlocale && dlocale->arg)
 	{
+		builtin_locale = defGetString(dlocale);
 		dbcollate = defGetString(dlocale);
 		dbctype = defGetString(dlocale);
 	}
@@ -1018,8 +1020,16 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 		dbctype = src_ctype;
 	if (dblocprovider == '\0')
 		dblocprovider = src_locprovider;
+	if (builtin_locale == NULL && dblocprovider == COLLPROVIDER_BUILTIN &&
+		src_locprovider == COLLPROVIDER_BUILTIN)
+		builtin_locale = "C";
 	if (dbiculocale == NULL && dblocprovider == COLLPROVIDER_ICU)
-		dbiculocale = src_iculocale;
+	{
+		if (dlocale && dlocale->arg)
+			dbiculocale = defGetString(dlocale);
+		else
+			dbiculocale = src_iculocale;
+	}
 	if (dbicurules == NULL && dblocprovider == COLLPROVIDER_ICU)
 		dbicurules = src_icurules;
 
@@ -1033,12 +1043,14 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	if (!check_locale(LC_COLLATE, dbcollate, &canonname))
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("invalid locale name: \"%s\"", dbcollate)));
+				 errmsg("invalid LC_COLLATE locale name: \"%s\"", dbcollate),
+				 errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
 	dbcollate = canonname;
 	if (!check_locale(LC_CTYPE, dbctype, &canonname))
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("invalid locale name: \"%s\"", dbctype)));
+				 errmsg("invalid LC_CTYPE locale name: \"%s\"", dbctype),
+				 errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
 	dbctype = canonname;
 
 	check_encoding_locale_matches(encoding, dbcollate, dbctype);
@@ -1060,11 +1072,38 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 		ereport(NOTICE,
 				(errmsg("using locale provider \"builtin\" for ICU locale \"%s\"",
 						dbiculocale)));
+		builtin_locale = dbiculocale;
 		dbiculocale = NULL;
 		dblocprovider = COLLPROVIDER_BUILTIN;
 	}
 
-	if (dblocprovider == COLLPROVIDER_ICU)
+	if (dblocprovider == COLLPROVIDER_BUILTIN)
+	{
+		/* can happen if template is a different provider */
+		if (builtin_locale == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("LOCALE must be specified")));
+
+		if (dbiculocale)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("ICU locale cannot be specified unless locale provider is ICU")));
+
+		if (dbicurules)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("ICU rules cannot be specified unless locale provider is ICU")));
+
+		if (strcmp(builtin_locale, "C") != 0 &&
+			strcmp(builtin_locale, "POSIX") != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("locale provider \"builtin\" does not support locale \"%s\"",
+							builtin_locale),
+					 errhint("The built-in locale provider only supports the \"C\" and \"POSIX\" locales.")));
+	}
+	else if (dblocprovider == COLLPROVIDER_ICU)
 	{
 		if (!(is_encoding_supported_by_icu(encoding)))
 			ereport(ERROR,
@@ -1079,7 +1118,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 		if (!dbiculocale)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("ICU locale must be specified")));
+					 errmsg("LOCALE or ICU_LOCALE must be specified")));
 
 		/*
 		 * During binary upgrade, or when the locale came from the template
@@ -1094,7 +1133,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 			if (langtag && strcmp(dbiculocale, langtag) != 0)
 			{
 				ereport(NOTICE,
-						(errmsg("using standard form \"%s\" for locale \"%s\"",
+						(errmsg("using standard form \"%s\" for ICU locale \"%s\"",
 								langtag, dbiculocale)));
 
 				dbiculocale = langtag;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 897521cf35..0a9307df2b 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -2163,7 +2163,11 @@ check_locale_name(int category, const char *locale, char **canonname)
 	if (res == NULL)
 	{
 		if (*locale)
-			pg_fatal("invalid locale name \"%s\"", locale);
+		{
+			pg_log_error("invalid locale name \"%s\"", locale);
+			pg_log_error_hint("If the locale name is specific to ICU, use --icu-locale.");
+			exit(1);
+		}
 		else
 		{
 			/*
@@ -2375,8 +2379,9 @@ static void
 setlocales(void)
 {
 	char	   *canonname;
+	char	   *builtin_locale = locale;
 
-	/* set empty lc_* values to locale config if set */
+	/* set empty lc_* and iculocale values to locale config if set */
 
 	if (locale)
 	{
@@ -2392,6 +2397,8 @@ setlocales(void)
 			lc_monetary = locale;
 		if (!lc_messages)
 			lc_messages = locale;
+		if (!icu_locale && locale_provider == COLLPROVIDER_ICU)
+			icu_locale = locale;
 	}
 
 	/*
@@ -2407,6 +2414,7 @@ setlocales(void)
 	{
 		pg_log_info("using locale provider \"builtin\" for ICU locale \"%s\"",
 					 icu_locale);
+		builtin_locale = "C";
 		icu_locale = NULL;
 		locale_provider = COLLPROVIDER_BUILTIN;
 	}
@@ -2434,7 +2442,15 @@ setlocales(void)
 	lc_messages = canonname;
 #endif
 
-	if (locale_provider == COLLPROVIDER_ICU)
+	if (locale_provider == COLLPROVIDER_BUILTIN)
+	{
+		if (!builtin_locale)
+			pg_fatal("The parameter --locale must be specified for the built-in provider.");
+		if (strcmp(builtin_locale, "C") != 0 &&
+			strcmp(builtin_locale, "POSIX") != 0)
+			pg_fatal("The built-in locale provider only supports the \"C\" and \"POSIX\" locales.");
+	}
+	else if (locale_provider == COLLPROVIDER_ICU)
 	{
 		char	   *langtag;
 
@@ -2687,7 +2703,9 @@ setup_locale_encoding(void)
 		 * If ctype_enc=SQL_ASCII, it's compatible with any encoding. ICU does
 		 * not support SQL_ASCII, so select UTF-8 instead.
 		 */
-		if (locale_provider == COLLPROVIDER_ICU && ctype_enc == PG_SQL_ASCII)
+		if ((locale_provider == COLLPROVIDER_BUILTIN ||
+			 locale_provider == COLLPROVIDER_ICU)
+			&& ctype_enc == PG_SQL_ASCII)
 			ctype_enc = PG_UTF8;
 
 		if (ctype_enc == -1)
@@ -3294,7 +3312,6 @@ main(int argc, char *argv[])
 				break;
 			case 8:
 				locale = "C";
-				locale_provider = COLLPROVIDER_LIBC;
 				break;
 			case 9:
 				pwfilename = pg_strdup(optarg);
diff --git a/src/bin/initdb/t/001_initdb.pl b/src/bin/initdb/t/001_initdb.pl
index f3f832a413..9b0c2202a3 100644
--- a/src/bin/initdb/t/001_initdb.pl
+++ b/src/bin/initdb/t/001_initdb.pl
@@ -193,11 +193,19 @@ else
 		'locale provider ICU fails since no ICU support');
 }
 
-command_ok(
+command_fails(
 	[
 		'initdb', '--no-sync', '--locale-provider=builtin', "$tempdir/data6"
 	],
-	'locale provider builtin'
+	'locale provider builtin without --locale'
+);
+
+command_fails(
+	[
+		'initdb', '--no-sync', '--locale-provider=builtin', '--locale=en',
+		"$tempdir/data6"
+	],
+	'locale provider builtin with invalid --locale'
 );
 
 command_ok(
@@ -210,32 +218,32 @@ command_ok(
 
 command_ok(
 	[
-		'initdb', '--no-sync', '--locale-provider=builtin', '--lc-collate=C',
-		"$tempdir/data8"
+		'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',
+		'--lc-collate=C', "$tempdir/data8"
 	],
 	'locale provider builtin with --lc-collate'
 );
 
 command_ok(
 	[
-		'initdb', '--no-sync', '--locale-provider=builtin', '--lc-ctype=C',
-		"$tempdir/data9"
+		'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',
+		'--lc-ctype=C', "$tempdir/data9"
 	],
 	'locale provider builtin with --lc-ctype'
 );
 
 command_fails(
 	[
-		'initdb', '--no-sync', '--locale-provider=builtin', '--icu-locale=en',
-		"$tempdir/dataX"
+		'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',
+		'--icu-locale=en', "$tempdir/dataX"
 	],
 	'fails for locale provider builtin with ICU locale'
 );
 
 command_fails(
 	[
-		'initdb', '--no-sync', '--locale-provider=builtin', '--icu-rules=""',
-		"$tempdir/dataX"
+		'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',
+		'--icu-rules=""', "$tempdir/dataX"
 	],
 	'fails for locale provider builtin with ICU rules'
 );
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c75818c3a4..8dbbd87610 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3080,13 +3080,10 @@ dumpDatabase(Archive *fout)
 		pg_fatal("unrecognized locale provider: %s",
 				 datlocprovider);
 
-	if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)
-	{
-		appendPQExpBufferStr(creaQry, " LOCALE = ");
-		appendStringLiteralAH(creaQry, collate, fout);
-	}
-	else
+	if (datlocprovider[0] == 'b')
 	{
+		appendPQExpBufferStr(creaQry, " LOCALE = 'C'");
+
 		if (strlen(collate) > 0)
 		{
 			appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
@@ -3098,15 +3095,51 @@ dumpDatabase(Archive *fout)
 			appendStringLiteralAH(creaQry, ctype, fout);
 		}
 	}
-	if (iculocale)
+	else if (datlocprovider[0] == 'i')
 	{
-		appendPQExpBufferStr(creaQry, " ICU_LOCALE = ");
+		Assert(iculocale);
+
+		appendPQExpBufferStr(creaQry, " LOCALE = ");
 		appendStringLiteralAH(creaQry, iculocale, fout);
+
+		if (strlen(collate) > 0)
+		{
+			appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
+			appendStringLiteralAH(creaQry, collate, fout);
+		}
+		if (strlen(ctype) > 0)
+		{
+			appendPQExpBufferStr(creaQry, " LC_CTYPE = ");
+			appendStringLiteralAH(creaQry, ctype, fout);
+		}
+		if (icurules)
+		{
+			appendPQExpBufferStr(creaQry, " ICU_RULES = ");
+			appendStringLiteralAH(creaQry, icurules, fout);
+		}
 	}
-	if (icurules)
+	else if (datlocprovider[0] == 'c')
 	{
-		appendPQExpBufferStr(creaQry, " ICU_RULES = ");
-		appendStringLiteralAH(creaQry, icurules, fout);
+		Assert(!iculocale);
+
+		if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)
+		{
+			appendPQExpBufferStr(creaQry, " LOCALE = ");
+			appendStringLiteralAH(creaQry, collate, fout);
+		}
+		else
+		{
+			if (strlen(collate) > 0)
+			{
+				appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
+				appendStringLiteralAH(creaQry, collate, fout);
+			}
+			if (strlen(ctype) > 0)
+			{
+				appendPQExpBufferStr(creaQry, " LC_CTYPE = ");
+				appendStringLiteralAH(creaQry, ctype, fout);
+			}
+		}
 	}
 
 	/*
diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.c
index 41a8de659e..8f8995964c 100644
--- a/src/bin/scripts/createdb.c
+++ b/src/bin/scripts/createdb.c
@@ -164,14 +164,6 @@ main(int argc, char *argv[])
 			exit(1);
 	}
 
-	if (locale)
-	{
-		if (!lc_ctype)
-			lc_ctype = locale;
-		if (!lc_collate)
-			lc_collate = locale;
-	}
-
 	if (encoding)
 	{
 		if (pg_char_to_encoding(encoding) < 0)
@@ -219,6 +211,11 @@ main(int argc, char *argv[])
 		appendPQExpBuffer(&sql, " STRATEGY %s", fmtId(strategy));
 	if (template)
 		appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
+	if (locale)
+	{
+		appendPQExpBufferStr(&sql, " LOCALE ");
+		appendStringLiteralConn(&sql, locale, conn);
+	}
 	if (lc_collate)
 	{
 		appendPQExpBufferStr(&sql, " LC_COLLATE ");
diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.pl
index 121ad5112b..3f5719c913 100644
--- a/src/bin/scripts/t/020_createdb.pl
+++ b/src/bin/scripts/t/020_createdb.pl
@@ -115,7 +115,7 @@ else
 $node->command_ok(
 	[
 		'createdb', '-T', 'template0', '--locale-provider=builtin',
-		'tbuiltin1'
+		'--locale=C', 'tbuiltin1'
 	],
 	'create database with provider "builtin"'
 );
@@ -131,7 +131,7 @@ $node->command_ok(
 $node->command_ok(
 	[
 		'createdb', '-T', 'template0', '--locale-provider=builtin',
-		'--lc-collate=C', 'tbuiltin3'
+		'--locale=C', '--lc-collate=C', 'tbuiltin3'
 	],
 	'create database with provider "builtin" and LC_COLLATE=C'
 );
@@ -139,7 +139,7 @@ $node->command_ok(
 $node->command_ok(
 	[
 		'createdb', '-T', 'template0', '--locale-provider=builtin',
-		'--lc-ctype=C', 'tbuiltin4'
+		'--locale=C', '--lc-ctype=C', 'tbuiltin4'
 	],
 	'create database with provider "builtin" and LC_CTYPE=C'
 );
@@ -168,6 +168,22 @@ $node->command_fails(
 	'create database with provider "builtin" not matching template'
 );
 
+$node->command_fails(
+	[
+		'createdb', '-T', 'template0', '--locale-provider=builtin',
+		'tbuiltin8'
+	],
+	'create database with provider "builtin" and locale unspecified'
+);
+
+$node->command_fails(
+	[
+		'createdb', '-T', 'template0', '--locale-provider=builtin',
+		'--locale=en', 'tbuiltin8'
+	],
+	'create database with provider "builtin" and locale=en'
+);
+
 $node->command_fails([ 'createdb', 'foobar1' ],
 	'fails if database already exists');
 
@@ -184,7 +200,7 @@ ALTER TABLE tab_foobar owner to role_foobar;
 CREATE POLICY pol_foobar ON tab_foobar FOR ALL TO role_foobar;');
 $node->issues_sql_like(
 	[ 'createdb', '-l', 'C', '-T', 'foobar2', 'foobar3' ],
-	qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2/,
+	qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2 LOCALE 'C'/,
 	'create database with template');
 ($ret, $stdout, $stderr) = $node->psql(
 	'foobar3',
@@ -211,7 +227,7 @@ $node->command_checks_all(
 	1,
 	[qr/^$/],
 	[
-		qr/^createdb: error: database creation failed: ERROR:  invalid locale name|^createdb: error: database creation failed: ERROR:  new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s
+		qr/^createdb: error: database creation failed: ERROR:  invalid LC_COLLATE locale name|^createdb: error: database creation failed: ERROR:  new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s
 	],
 	'createdb with incorrect --lc-collate');
 $node->command_checks_all(
@@ -219,7 +235,7 @@ $node->command_checks_all(
 	1,
 	[qr/^$/],
 	[
-		qr/^createdb: error: database creation failed: ERROR:  invalid locale name|^createdb: error: database creation failed: ERROR:  new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s
+		qr/^createdb: error: database creation failed: ERROR:  invalid LC_CTYPE locale name|^createdb: error: database creation failed: ERROR:  new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s
 	],
 	'createdb with incorrect --lc-ctype');
 
diff --git a/src/test/icu/t/010_database.pl b/src/test/icu/t/010_database.pl
index 26f71e1155..6d19488afc 100644
--- a/src/test/icu/t/010_database.pl
+++ b/src/test/icu/t/010_database.pl
@@ -51,17 +51,23 @@ b),
 	'sort by explicit collation upper first');
 
 
-# Test error cases in CREATE DATABASE involving locale-related options
-
-my ($ret, $stdout, $stderr) = $node1->psql('postgres',
-	q{CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}
+# Test that LOCALE='C' works for ICU
+my $ret1 = $node1->psql('postgres',
+	q{CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}
 );
-isnt($ret, 0,
-	"ICU locale must be specified for ICU provider: exit code not 0");
+is($ret1, 0,
+	"C locale works for ICU");
+
+# Test that ICU-specific locale string must be specified with ICU_LOCALE,
+# not LOCALE
+my ($ret2, $stdout, $stderr) = $node1->psql('postgres',
+	q{CREATE DATABASE dbicu3 LOCALE_PROVIDER icu LOCALE '@colStrength=primary' TEMPLATE template0 ENCODING UTF8});
+isnt($ret2, 0,
+	"ICU-specific locale must be specified with ICU_LOCALE: exit code not 0");
 like(
 	$stderr,
-	qr/ERROR:  ICU locale must be specified/,
-	"ICU locale must be specified for ICU provider: error message");
+	qr/ERROR:  invalid LC_COLLATE locale name/,
+	"ICU-specific locale must be specified with ICU_LOCALE: error message");
 
 my ($ret, $stdout, $stderr) = $node1->psql('postgres',
 	q{CREATE DATABASE dbicu LOCALE_PROVIDER builtin LOCALE 'C' TEMPLATE dbicu}
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 5ba8f75558..9e49c0d9a7 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1202,9 +1202,9 @@ SELECT 'coté' < 'côte' COLLATE "und-x-icu", 'coté' > 'côte' COLLATE testcoll
 (1 row)
 
 CREATE COLLATION testcoll_lower_first (provider = icu, locale = '@colCaseFirst=lower');
-NOTICE:  using standard form "und-u-kf-lower" for locale "@colCaseFirst=lower"
+NOTICE:  using standard form "und-u-kf-lower" for ICU locale "@colCaseFirst=lower"
 CREATE COLLATION testcoll_upper_first (provider = icu, locale = '@colCaseFirst=upper');
-NOTICE:  using standard form "und-u-kf-upper" for locale "@colCaseFirst=upper"
+NOTICE:  using standard form "und-u-kf-upper" for ICU locale "@colCaseFirst=upper"
 SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcoll_upper_first;
  ?column? | ?column? 
 ----------+----------
@@ -1212,7 +1212,7 @@ SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcol
 (1 row)
 
 CREATE COLLATION testcoll_shifted (provider = icu, locale = '@colAlternate=shifted');
-NOTICE:  using standard form "und-u-ka-shifted" for locale "@colAlternate=shifted"
+NOTICE:  using standard form "und-u-ka-shifted" for ICU locale "@colAlternate=shifted"
 SELECT 'de-luge' < 'deanza' COLLATE "und-x-icu", 'de-luge' > 'deanza' COLLATE testcoll_shifted;
  ?column? | ?column? 
 ----------+----------
@@ -1229,12 +1229,12 @@ SELECT 'A-21' > 'A-123' COLLATE "und-x-icu", 'A-21' < 'A-123' COLLATE testcoll_n
 (1 row)
 
 CREATE COLLATION testcoll_error1 (provider = icu, locale = '@colNumeric=lower');
-NOTICE:  using standard form "und-u-kn-lower" for locale "@colNumeric=lower"
+NOTICE:  using standard form "und-u-kn-lower" for ICU locale "@colNumeric=lower"
 ERROR:  could not open collator for locale "und-u-kn-lower": U_ILLEGAL_ARGUMENT_ERROR
 -- test that attributes not handled by icu_set_collation_attributes()
 -- (handled by ucol_open() directly) also work
 CREATE COLLATION testcoll_de_phonebook (provider = icu, locale = 'de@collation=phonebook');
-NOTICE:  using standard form "de-u-co-phonebk" for locale "de@collation=phonebook"
+NOTICE:  using standard form "de-u-co-phonebk" for ICU locale "de@collation=phonebook"
 SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE testcoll_de_phonebook;
  ?column? | ?column? 
 ----------+----------
@@ -1243,7 +1243,7 @@ SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE tes
 
 -- rules
 CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a < g');
-NOTICE:  using standard form "und" for locale ""
+NOTICE:  using standard form "und" for ICU locale ""
 CREATE TABLE test7 (a text);
 -- example from https://unicode-org.github.io/icu/userguide/collation/customization/#syntax
 INSERT INTO test7 VALUES ('Abernathy'), ('apple'), ('bird'), ('Boston'), ('Graham'), ('green');
@@ -1271,13 +1271,13 @@ SELECT * FROM test7 ORDER BY a COLLATE testcoll_rules1;
 
 DROP TABLE test7;
 CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!');
-NOTICE:  using standard form "und" for locale ""
+NOTICE:  using standard form "und" for ICU locale ""
 ERROR:  could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ERROR
 -- nondeterministic collations
 CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true);
-NOTICE:  using standard form "und" for locale ""
+NOTICE:  using standard form "und" for ICU locale ""
 CREATE COLLATION ctest_nondet (provider = icu, locale = '', deterministic = false);
-NOTICE:  using standard form "und" for locale ""
+NOTICE:  using standard form "und" for ICU locale ""
 CREATE TABLE test6 (a int, b text);
 -- same string in different normal forms
 INSERT INTO test6 VALUES (1, U&'\00E4bc');
@@ -1327,9 +1327,9 @@ SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
 (2 rows)
 
 CREATE COLLATION case_sensitive (provider = icu, locale = '');
-NOTICE:  using standard form "und" for locale ""
+NOTICE:  using standard form "und" for ICU locale ""
 CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
-NOTICE:  using standard form "und-u-ks-level2" for locale "@colStrength=secondary"
+NOTICE:  using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary"
 SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
  ?column? | ?column? 
 ----------+----------
-- 
2.34.1