Create databases in PostgreSQL

April 29, 2021  ‐ 1 min read

Creating a database in PostgreSQL is done via an SQL statement, and you need the CREATEDB role to be able to do this. In its simplest form the SQL statement looks as follows:

CREATE DATABASE myproject;

Afterwards you may connect to the database with the meta command: \c <database_name>.

What creating a database in PostgreSQL actually does is making a copy from a template database. A template database is exactly what the name suggests it is, it serves as a template for a newly created database. PostgreSQL copies copies over the settings and data from the template to the database you create.

PostgreSQL comes with two template databases: template0 and template1, and uses template1 by default when you create a new database. The template0 is supposed to be a sort of safety net, if you mess something up while customizing template1 you can still recover it from template0. Therefore it is advised to make all your customizations in template1 and never change template0.

When creating a database you can specify your preferred template database.

CREATE DATABASE myproject TEMPLATE django_template;