Introduction
PostgreSQL is an open-source, advanced database management system supporting relational (SQL) and non-relational (JSON) querying.
In PostgreSQL, the emphasis is mainly on extensibility and SQL compliance while maintaining ACID properties (Atomicity, Consistency, Isolation, Durability) in transactions.
In this tutorial, you will learn how to create a database in PostgreSQL using three different methods.
Prerequisites
- PostgreSQL installed and set up
- Administrator privileges
Create a Database in PostgreSQL via pgAdmin
To create a database using pgAdmin, follow these steps:
Step 1: Open pgAdmin and enter your password to connect to the database server.
Step 2: In the browser section, expand the Servers and then PostgreSQL items. Right-click the Databases item. Click Create and Database…
Step 3: A new window pops up where you need to specify the database name, add a comment if necessary and click Save.
The database appears in the Databases object tree.
The right section of the pgAdmin window contains tabs that display database statistics, SQL commands used to create the database, any dependencies, etc.
Note: Pay attention to data type when inserting data into the database. Read our article to learn the different data types in MySQL.
Create a Database in PostgreSQL via CREATE DATABASE
Another method to create a PostrgreSQL database is to use the CREATE DATABASE command.
Follow these steps to create a database:
Step 1: Open the SQL Shell (psql) app.
Step 2: Connect to the DB server by pressing ENTER four times. Type in your master password if asked. If you didn't set up a password, press ENTER again to connect.
Step 3: Use the following syntax to create a new database:
CREATE DATABASE [database_name]In place of [database_name], enter a name for your database. Make sure to enter a unique name because using an existing database name results in an error.
For example:
Creating a database without specifying any parameters takes the parameters from the default template database. See the available parameters in the next section.
Step 4: Use the following command to list all the databases in PostgreSQL:
\l
The output shows a list of available databases and their characteristics.
CREATE DATABASE Parameters
The available parameters for creating a database are:
[OWNER = role_name]
The OWNER parameter assigns the database owner role. Omitting the OWNER parameter means that the database owner is the role used to execute the CREATE DATABASE statement.
[TEMPLATE = template]
The TEMPLATE parameter allows you to specify the template database from which to create the new database. Omitting the TEMPLATE parameter sets template1 as the default template database.
[ENCODING = encoding]
The ENCODING parameter determines the character set encoding in the new database.
[LC_COLLATE = collate]
The LC_COLLATE parameter specifies the collation order of the new database. This parameter controls the string sort order in the ORDER BY clause. The effect is visible when using a locale that contains special characters.
Omitting the LC_COLLATE parameter takes the settings from the template database.
[LC_CTYPE = ctype]
The LC_CTYPE parameter specifies the character classification used in the new database. Character classification includes lower, upper case, and digits. Omitting the LC_CTYPE parameter takes the default settings from the template database.
[TABLESPACE = tablespace_name]
Use the TABLESPACE parameter to specify the tablespace name for the new database. Omitting the TABLESPACE parameter takes the tablespace name of the template database.
[ALLOW_CONNECTIONS = true | false]
The ALLOW_CONNECTIONS parameter can be TRUE or FALSE. Specifying the value as FALSE prevents you from connecting to the database.
[CONNECTION LIMIT = max_concurrent_connections]
The CONNECTION LIMIT parameter lets you to set the maximum simultaneous connections to a PostgreSQL database. The default value is -1, which means unlimited connections.
[IS_TEMPLATE = true | false ]
Set the IS_TEMPLATE parameter to TRUE or FALSE. Setting IS_TEMPLATE to TRUE allows any user with the CREATEDB privilege to clone the database. Otherwise, only superusers or the database owner can clone the database.
To create a database with parameters, add the keyword WITH after the CREATE DATABASE statement and then list the parameters you want.
For example:
This example shows how to set a new database to use the UTF-8 character encoding and to support a maximum of 200 concurrent connections.
Create a Database in PostgreSQL via createdb Command
The createdb command is the third method for creating a database in PostgreSQL. The only difference between the createdb and CREATE DATABASE command is that users run createdb directly from the command line and add a comment into the database, all at once.
To create a database using the createdb command, use the following syntax:
createdb [argument] [database_name [comment]]The parameters are discussed in the following section.
Note: createdb internally runs CREATE DATABASE from psql while connected to the template1 database. The user creating the database is the only DBA and the only one who can drop the database, other than the postgres superuser.
createdb Parameters
The createdb syntax parameters are:
| Parameter | Description |
|---|---|
[argument] | Command-line arguments that createdb accepts. Discussed in the next section. |
[database_name] | Set the database name in place of the database_name parameter. |
[comment] | Optional comment to be associated with the new database. |
createdb Command Line Arguments
The available createdb arguments are:
| Argument | Description |
|---|---|
-D | Specifies the tablespace name for the new database. |
-e | Shows the commands that createdb sends to the server. |
-E | Specifies which character encoding to use in the database. |
-l | Specifies which locale to use in the database. |
-T | Specifies which database to use as a template for the new database. |
--help | Show help page about the createdb command line arguments. |
-h | Displays the hostname of the machine running the server. |
-p | Sets the TCP port or the local Unix domain socket file extension which the server uses to listen for connections. |
-U | Specifies which username to use to connect. |
-w | Instructs createdb never to issue a password prompt. |
-W | Instructs createdb to issue a password prompt before connecting to a database. |
For example:
Here, we created a database called mydatabase using the default admin user postgres. We used the phoenixnap database as a template and instructed the program not to ask for a password.
createdb Command Common Errors
There are some common errors users may encounter when using the createdb command.
See the createdb error list below:
| Error | Description |
|---|---|
createdb command not found. | Occurs when PostgreSQL was not installed properly. Run createdb from the PostgreSQL installation path or add the psql.exe path to the system variables section in PC advanced settings. |
| No such file or directory Is the server running locally and accepting connections on Unix domain socket ...? | Happens when the PostgreSQL server wasn't properly started or is not currently running. |
| Fatal: role "username" does not exist. | Occurs when users run initdb with a role without superuser privileges.To fix the error, create a new Postgres user with the --superuser option or login to the default admin role, postgres. |
| Database creation failed: ERROR: permission denied to create database. | Appears when trying to create a database with an account that doesn't have the necessary permissions. To fix the error, grant superuser permissions to the role in question. |
Conclusion
Now you know how to create a database in PostgreSQL using three different methods. If you prefer a GUI environment, use pgAdmin, or use the CLI or SQL Shell if you prefer running SQL commands.
To find out how can you delete an existing PostgreSQL database, read our article PostgreSQL drop database.
If you are interested to learn more about PostgreSQL, make sure to check how to install PostgreSQL Workbench, how to Export PostgreSQL Table to CSV, how to Check PostgreSQL Version, how to Download and Install PostgreSQL on Windows, or how to use the PostgreSQL SELECT Statement.
And for information about different built-in data types available in PostgreSQL, you might find helpful our article PostgreSQL data types.
