This section describes how to create a database in PostgreSQL
PostgreSQL is an open-source, high-level database management system that supports both relational (SQL) and non-relational (JSON) queries. In PostgreSQL, the focus is primarily on scalability 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, including the corresponding PostgreSQL database creation example. Prerequisite.
- Install and set up PostgreSQL
- Administrator privileges
Create a database in PostgreSQL via pgAdmin
How Do I Create a PostgreSQL Database? 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 Browsers section, expand Servers and then expand the PostgreSQL project. Right-click the database item. Click Create and Database…
Step 3: PostgreSQL How to create a database: a new window pops up where you need to specify the database name, add comments if necessary and click “Save“.
The database appears in the database object tree. The right part of the pgAdmin window contains tabs that display database statistics, SQL commands for creating the database, any dependencies, and so on. Note: Be mindful of the data type when inserting data into the database. Read our article to learn about the different data types in MySQL.
Create a database in PostgreSQL with CREATE DATABASE
How Do I Create a PostgreSQL Database? Another way to create a PostgreSQL database is to use the CREATE DATABASE command. Follow these steps to create a database: Step 1: Open the SQL Shell (psql) application.
Step 2: Press ENTER four times to connect to the database server. If asked, enter your master password. If you don’t have a password, press ENTER again to connect.
Step 3: Create a new database using the following syntax: Instead of [database_name], enter a name for the database. Be sure to enter a unique name, as using an existing database name will result in an error. For example:
CREATE DATABASE [database_name]
PostgreSQL creates a database: creates a database that does not specify any parameters and obtains parameters from the default template database. See the available parameters in the next section. Step 4: List all databases in PostgreSQL with the following command:
\l
The output displays a list of available databases and their characteristics.
Example for creating a database in PostgreSQL: Create a database parameter
The parameters available for creating a database are:
[OWNER = role_name]
The OWNER parameter assigns the database owner role. The omission of 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 THE NEW DATABASE WILL BE CREATED. Omit the TEMPLATE parameter to set template1 as the default template database.
[ENCODING = encoding]
THIS ENCODING PARAMETER DETERMINES THE CHARACTER SET ENCODING IN THE NEW DATABASE.
[LC_COLLATE = collate]
This LC_COLLATE parameter specifies the order in which the new database is organized. This parameter controls the sort order of strings in the ORDER BY clause. The effect is visible when using a locale that contains special characters. Omitting LC_COLLATE parameter takes the settings from the template database.
[LC_CTYPE = ctype]
This LC_CTYPE parameter specifies the character classification to be used in the new database. Character classification includes lowercase, uppercase, and numbers. The omitted LC_CTYPE parameter adopts the default settings in the template database.
[TABLESPACE = tablespace_name]
USE THE TABLESPACE PARAMETER TO SPECIFY A TABLESPACE NAME FOR THE NEW DATABASE. The TABLESPACE omitted parameter uses the name of the tablespace in the template database.
[ALLOW_CONNECTIONS = true | false]
The ALLOW_CONNECTIONS parameter can be TRUE or FALSE. Specifying a value of FALSE prevents you from connecting to the database.
[CONNECTION LIMIT = max_concurrent_connections]
The CONNECTION LIMIT parameter allows you to set the maximum number of 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. Set the IS_TEMPLATE to TRUE to allow any user with CREATEDB clone database permissions. Otherwise, only a superuser or database owner can clone a database. To create a database with parameters, add the WITH keyword after the CREATE DATABASE statement and then list the required parameters. For example:
This example shows how to set up a new database to use UTF-8 character encoding and support up to 200 concurrent connections.
Use the createdb command to create a database in PostgreSQL
How Do I Create a PostgreSQL Database? The createdb command is the third way to create a database in PostgreSQL. The only difference between the createdb and CREATE DATABASE commands is that the user createdbruns directly from the command line and adds comments to the the database all at once. PostgreSQL method of creating a database: To create a database using the createdb command, use the following syntax: These parameters are discussed in the next section. Note: Run CREATE DATABASE from inside psqlcreatedb when connecting to the template1 database. With the exception of the Postgres superuser, the user who created the database is the only DBA and the only user who can delete the database.
createdb [argument] [database_name [comment]]Example for creating a database in PostgreSQL: createdb parameter
The createdb syntax arguments:
| range | description |
|---|---|
[argument] | Command-line arguments accepted by createdb. Discussed in the next section. |
[database_name] | Set the database name instead of the database_name parameter. |
[comment] | An optional comment associated with the new database. |
createdb command-line argument
The available createdb parameters are:
| debate | description |
|---|---|
-D | Specify the name of the tablespace for the new database. |
-e | Displays the command that createdb sent to the server. |
-E | Specify the character encoding to be used in the database. |
-l | Specify the locale to be used in the database. |
-T | Specify the database to use as a template for the new database. |
--help | Displays a help page about createdb command-line arguments. |
-h | Displays the hostname of the machine on which the server is running. |
-p | Set the TCP port or local Unix domain socket file extension that the server uses to listen for connections. |
-U | Specify the username used for the connection. |
-w | Instructs createdb to never give a password hint. |
-W | Instructs createdb to issue a password hint before connecting to the database. |
For example:
Here we have created a database called mydatabase with the default admin user postgres. We use the Phoenixnap database as a template and instruct the program not to ask for passwords.
createdb command
There are some common errors that users may encounter while using the createdb command. See createdb for a list of errors below:
| mistake | description |
|---|---|
createdb could not find the relevant command. | Occurs when PostgreSQL is installed incorrectly. createdb runs from the PostgreSQL installation path or adds the psql .exe path to the System Variables section in the PC’s advanced settings. |
| There are no such files or directories Whether the server is running locally and accepting Connections on Unix domain sockets…? | Occurs when the PostgreSQL server is not starting properly or is not currently running. |
| Fatal: The role ‘username’ does not exist. | Occurs when user initdb is running in a role that does not have superuser privileges. To fix the error, use the --superuser option to create a new user or log in to the default admin role postgres. |
| Database Creation Failed: Error: Permission to create database was denied. | Appears when trying to create a database with an account that does not have the necessary permissions. To fix the error, grant superuser privileges to the relevant role. |
conclusion
How Do I Create a PostgreSQL Database? Now you know how to create a database using three different PostgreSQL methods. If you prefer a GUI environment, use pgAdmin, or if you prefer to run SQL commands, use the CLI or SQL Shell. If you’re interested in learning more about PostgreSQL, be sure to check out how to install PostgreSQL Workbench, how to export PostgreSQL tables to CSV, how to check the PostgreSQL version, how to download and install PostgreSQL on Windows, or how to use PostgreSQL SELECT declarations. For information about the different built-in data types available in PostgreSQL, you may find our article PostgreSQL data types helpful.
