PostgreSQL Create Database Options and Settings

By:   |   Updated: 2024-02-26   |   Comments   |   Related: > PostgreSQL


One of the first actions a DBA needs to perform on a new RDBMS instance is to create a database. Let's explore how this can be done in PostgreSQL, including the syntax, particularities, and differences with other RDBMS.


This tip will show all the peculiarities, features, and different syntaxes of the CREATE DATABASE command in PostgreSQL.


The basic syntax of the CREATE DATABASE command is fairly simple and straightforward: Type CREATE DATABASE and the name of the database, and you're done. However, to do so, you must have the required privileges as a superuser or have the CREATEDB permissions.

A quick digression on superuser: In PostgreSQL, it is a role quite similar to the SA of SQL Server, and the default created at PostgreSQL setup is the superuser postgres. We will return to this topic when I write a more in-depth article about ROLES. Anyway, this topic was briefly touched on in this two-part tip:

Now, let's return to the CREATE DATABASE statement and its options for a new database.


First, we can specify the role of the database owner. If not specified, it is, by default, the one that we are using for the connection. I'll return to this later.

As is common with many other RDBMS, we can then define the collation of the database using the LOCALE option. This collation can be explicitly specified. If not, the default one of the template database will be used. However, only the collations with the libcprovider, directly obtained from the operating system, can be used with this option.

Let's look at all available collations in the PostgreSQL cluster, querying the catalog pg_collation:

select collname, colliculocale, collversion, collcollate, collctype, collprovider
from pg_collation;
query output

At this point, we can use the collation option of the CREATE DATABASE statement. One option is with the collprovider = 'c' (OS collations) or the second option is to specify both locale_provider and icu_locale. To use the icu options select one marked with 'i'.

Let's see an example of both for a new Postgres database:

create database test_collation template template0 locale='C.UTF-8';
query message
create database test_collation2 template template0 locale_provider='ICU' icu_locale 'de-AT';
query message

Note: In both cases, since we are using a collation different from the default, we have to specify template0 instead of the default, which is template1. We'll dive into templates in the next paragraph. In the second case, setting the locale_provider and icu_locale overrides the locale option. Let's take a look at the pgAdmin list of databases:

database list

And check the collation querying the pg_database catalog with the following command:

SELECT datname, datcollate AS collation, datlocprovider
FROM pg_database
where datname like 'test%';
query output

The default collation is still used in the test_collation2 database, but the locale provider is ICU. As mentioned before, the locale_provider and icu_locale override the locale parameter even though it is specified in the database collation.

Using ICU collations instead of operating system collations has an advantage in that they are independent from operating systems and their changes, thus avoiding possible data corruption.


We briefly introduced templates in the previous paragraph, but there is much to say about them. When creating a database, we always use a template, as in SQL Server with a model database. In PostgreSQL, we have two, as we saw before, template0 and template1, which are automatically created when we initialize the cluster. (Remember that in PostgreSQL, an instance is called a cluster.) As we have seen, template1 is the default, and template0 is used when you need to specify some not default options for database creation. But there is more to it. We can create our own template databases and even create a database copying from another one, albeit with some limitations.

So, suppose we need a database that should be cloned for test purposes. We have a couple of options using templates. One option is that we can make a new template database using the parameter is_template in the CREATE DATABASE command:

create database template_test with is_template=true allow_connections=false;
query message

Notice that I also specified that the allow_connections option is false; otherwise, we would have an error when cloning the database (more on that soon).

If we check the list of the databases from pgAdmin, we see that the template that we just created is not present:

database list

Now, we can create a database using this template:

create database test_template with template template_test;
query message

Now check the list:

database list

We can now drop the database we just created to show the other method (create a template database using an existing one).

drop database test_template;

First of all, we need to look at the pg_database table, in particular at some columns with the following SQL command:

select oid, datname, datistemplate, datallowconn
from pg_database
query output

As you notice, both template0 and template1 have the column datistemplate set to true. So, we can just set this column value to true and, just for security, the datallowconn one to false.

Let's update those columns on the test_collation2 database using the oid and see:

update pg_database
set datallowconn=false, datistemplate=true
where oid=68121;

Let's look again at pg_database:

query output

If we check the list of the databases present in this cluster with pgAdmin:

database list

We see that now test_collation2 is no longer listed.

Finally, we can create another database using this new template that is now test_collation2:

create database test_template with template test_collation2;
query message

We can check the list of databases in pgAdmin and also query again the pg_database catalog:

database list
SELECT datname, datcollate AS collation, datlocprovider, datistemplate
FROM pg_database
where datname like 'test%';
query output

In this case, we can see that the database test_template has been created with the same collation as test_template and that this one is not a template.

We can also create a database by cloning another without a template database. In this case, we must ensure that the database from which we copy has no active connections opened and that the user doing that is a superuser or the owner of the database. Instead, with templates, any account with the create database privilege can clone them.

Let's see an example. Open a connection with psql on the database Apache:

command line

Issue this simple query:

command line

Repeat this query every second using the psql command watch:

command line

This will simulate a continuous active session on the database Apache:

command line

Now, in another query editor in pgAdmin:

create database test_apache with template "Apache";
query message

As we can see, we cannot use this database as a template because there are sessions connected to it, so we stop our load in psql and clean up all the connected sessions:

command line

Check into the pgAdmin dashboard; there is still an idle connection:

server activity

We can kill the connection directly from the dashboard:

query output

Now we can issue the command again:

create database test_apache with template "Apache";
query message

That now works.


Another important point in the CREATE DATABASE command is the possibility of specifying a tablespace on which the database files will be stored. As the PostgreSQL documentation says: "Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation."

Essentially, a tablespace in PostgreSQL is a directory containing data files. These data files are the storage for database objects: tables, sequences, indexes, and materialized views. In PostgreSQL, each such object has its own data file (or more if it's big).

This is similar to the concept of file groups in SQL Server, where database objects that are heavily used can be stored on specific fast disks and other less used objects on lower-tier storage.

Pay attention to the special disclaimer for Oracle DBAs: The concept of PostgreSQL Tablespaces is completely different from Oracle! In fact, PostgreSQL uses the operating system's filesystem for storage, while Oracle implements its own file system. In PostgreSQL, a tablespace is a way to tell the RDBMS where the physical files of database objects are stored. It is not a part of the file system; it is only a link to where the objects are.

So, as a first step, we can create a new tablespace, different from the default tablespace pg_default, that is automatically created when we initialize a PostgreSQL instance, and that is used if we do not indicate it in the CREATE object command (obviously, we need to be superuser or have the CREATE TABLESPACE privilege to do so):

create tablespace test_tablespace location '/var/lib/postgresql/test';
query message

We can now query the pg_tablespace catalog to see the tablespaces present in our environment:

select *
from pg_tablespace;
query output

Now that we have defined a new tablespace, we can use it in order to create a database on it:

create database test_tablespace with tablespace=test_tablespace;
query message

We can now query the pg_database catalog joined with pg_tablespaces:

select datname, spcname
from pg_database
inner JOIN pg_tablespace t ON dattablespace = t.oid; 
query output

And we can see that database test_tablespace resides on the tablespace with the same name.


Last but not least, we need to talk about the owner of the database. If this is not specified in the CREATE DATABASE command, the owner will be the user/role login with which we are connected to PostgreSQL. Otherwise, we can define a different owner for the database.

Let's take a look with an example using an existing role created from a previous tip:

create database test_owner with owner="Finance";
query message

Let's check the pg_database catalog joining it with pg_roles one:

select dat.oid, datname, datdba, rolname
from pg_database dat
inner join pg_roles rol on datdba=rol.oid;
query output

We can see that the database that we just created has the owner (datdba column in pg_database) set to role Finance.

Next Steps

In this tip, we have reviewed the CREATE DATABASE command in PostgreSQL, checking some of the most important options and parameters and looking at some catalog table queries that can help check database objects in PostgreSQL.

As always, the first source of information on PostgreSQL is the official documentation: CREATE DATABASE.

Here are some links to other tips on CREATE DATABASE in SQL Server:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Andrea Gnemmi Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-02-26

Comments For This Article

get free sql tips
agree to terms