CREATE DATABASE (PostgreSQL)

https://www.postgresql.org/docs/current/sql-createdatabase.html

CREATE DATABASE name
   [ WITH ] [ OWNER [=] user_name ]
          [ TEMPLATE [=] template ]
          [ ENCODING [=] encoding ]
          [ STRATEGY [=] strategy ] ]
          [ LOCALE [=] locale ]
          [ LC_COLLATE [=] lc_collate ]
          [ LC_CTYPE [=] lc_ctype ]
          [ ICU_LOCALE [=] icu_locale ]
          [ LOCALE_PROVIDER [=] locale_provider ]
          [ COLLATION_VERSION = collation_version ]
          [ TABLESPACE [=] tablespace_name ]
          [ ALLOW_CONNECTIONS [=] allowconn ]
          [ CONNECTION LIMIT [=] connlimit ]
          [ IS_TEMPLATE [=] istemplate ]
          [ OID [=] oid ]

LOCALE available since Oct 2020 PostgreSQL 13

• createdb command include in postgresql-client-common package

PostgreSQL

CREATE DATABASE "yournewdbname";

CREATE DATABASE "yournewdbname"
 WITH OWNER "postgres"
 ENCODING 'UTF8'
 LC_COLLATE = 'en_US.UTF-8'
 LC_CTYPE = 'en_US.UTF-8';

CREATE DATABASE "scratch"
 WITH OWNER "postgres"
 ENCODING 'UTF8';

https://confluence.atlassian.com/doc/database-setup-for-postgresql-173244522.html

CREATE DATABASE "confluence"
 WITH OWNER "confluenceuser"
 ENCODING 'UTF8'
 LC_COLLATE = 'en_US.UTF-8'
 LC_CTYPE = 'en_US.UTF-8';

Output:

 CREATE DATABASE
or
 ERROR:  role "confluenceuser" does not exist
or
ERROR:  new collation (en_US.UTF-8) is incompatible with the collation of the template database (C.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.
or
 ERROR:  invalid locale name: "en_US.UTF-8"
 Soluction:  locale-gen en_US.UTF-8

Solution:

CREATE DATABASE "confluence"
 WITH OWNER "confluenceuser"
 ENCODING 'UTF8'
 LC_COLLATE = 'en_US.UTF-8'
 LC_CTYPE = 'en_US.UTF-8'
 TEMPLATE template0;

Output:

CREATE DATABASE

Createdb command

sudo su - postgres
createdb -E UNICODE -l C -T template0 YOUR_DB_NAME
(no output)

Timescale cloud

CREATE DATABASE test;
ERROR:  tsdb_admin: database test is not an allowed database name
HINT:  Contact your administrator to configure the 
"tsdb_admin.allowed_databases"

Solutions: is not allowed 

Related terms

• createdb command
• CREATE TABLE
• GRANT ALL PRIVILEGES ON DATABASE confluence TO confluenceuser;
• \list
• psql -l
• DROP DATABASE

See also

• CREATE [ DATABASE | INDEX | USER | ROLE | VIEW | TABLE | TRIGGER | FUNCTION | COLLATION ]
• psql -l

https://stackoverflow.com/questions/18870775/how-to-change-the-template-database-collection-coding

https://docs.timescale.com/cloud/latest/services/create-a-service/