PostgreSQL Create Database Tutorial and Examples

A database is a container for tables, and a database can contain multiple tables. To store data in PostgreSQL, you need to create a database first.

PostgreSQL allows you to create a new database using the CREATE DATABASE statement.

PostgreSQL CREATE DATABASE syntax

To create a database on a PostgreSQL server, you must be superuser or have CREATEDB privileges.

Please use the CREATE DATABASE statement according to the following syntax:

CREATE DATABASE db_name
[ [ WITH ] [ OWNER [=] user_name ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ LOCALE [=] locale ]
       [ LC_COLLATE [=] lc_collate ]
       [ LC_CTYPE [=] lc_ctype ]
       [ TABLESPACE [=] tablespace_name ]
       [ ALLOW_CONNECTIONS [=] allowconn ]
       [ CONNECTION LIMIT [=] connlimit ]
       [ IS_TEMPLATE [=] istemplate ] ]

It may seem like a complicated statement, but the most common one is the following one-line statement:

CREATE DATABASE db_name;

You can use the optional options in any order. The following is a description of the parameters:

db_name: The name of the database to create.
user_name: The role name of the user who will own the new database. You can use DEFAULT to indicate the user who executed the command.
template: The template name for creating new databases. You can use DEFAULT to represent the default template (template1).
encoding: The character set encoding to use in the new database. You can specify a string constant (for example, 'SQL_ASCII'), or an integer encoding number, or DEFAULT (the encoding of the template database). Click here to see the character sets supported by PostgreSQL.
locale: This is a shortcut to set LC_COLLATE and LC_CTYPE. If this is specified, neither of two parameters can be specified.
lc_collate: The collation orde (LC_COLLATE) to use in the new database. This affects the sort order applied to strings.
lc_ctype: The character classification (LC_CTYPE) to use in the new database. This affects the classification of characters, such as lowercase, uppercase, and numbers.
tablespace_name: The name of the tablespace that will be associated with the new database. You can use DEFAULT to use the tablespace of the template database.
allowconn: Whether to allow connections to this database. If it is false, no one can connect to the database. The default is true to allow connections.
connlimit: How many concurrent connections can be made to this database. -1 (default) means no limit.
istemplate: Whether it is a template database. If true, any user with CREATEDB privileges can clone this database; if false (default), only the superuser or database owner can clone it.

PostgreSQL Create Database Examples

In this example, you will create a database named testdb in the psql tool.

First, log in to the PostgreSQL server with your own user:

[~] psql -U postgres
psql (14.4)
Type "help" for help.

Second, create the testdb database:
```
CREATE DATABASE testdb;
```
```
CREATE DATABASE
```
If you enter an already existing database name, PostgreSQL will return an error: ERROR: database "testdb" already exists.
Finally, connect to the testdb database you just created using the \c command:
```
\c testdb;
```
```
You are now connected to database "testdb" as user "postgres".
```
Now, you can create a table and insert rows, update rows, delete rows, query rows, etc.

Typically, creating a database is a simple operation.

Conclusion

The PostgreSQL CREATE DATABASE statement is used to create a new database on the PostgreSQL server.

PostgreSQL CREATE DATABASE syntax

PostgreSQL Create Database Examples

Conclusion

On this page