PostgreSQL – Create Database
PostgreSQL has multiple ways to create a database. In this article we will discuss multiple ways to do so.
1. Using psql Shell:
To create a database through the psql shell we make the use of the
CREATE DATABASE
statement as below:
CREATE DATABASE db_name
OWNER = role_name
TEMPLATE = template
ENCODING = encoding
LC_COLLATE = collate
LC_CTYPE = ctype
TABLESPACE = tablespace_name
CONNECTION LIMIT = max_concurrent_connection
The various options provided by the CREATE DATABASE statement are explained below:
- db_name: It is the name of the new database that you want to create. It must always be a unique name.
- role_name: It is the role name of the user who will own the new database.
- template: It is the name of the database template from which the new database gets created.
- encoding: It specifies the character set encoding for the new database. By default, it is the encoding of the template database.
- collate: It specifies a collation for the new database.
- ctype: It specifies the character classification for the new database like digit, lower and upper.
- tablespace_name: It specifies the tablespace name for the new database.
- max_concurrent_connection: It specifies the maximum concurrent connections to the new database.
Example 1:
Here we will create a test database with all default settings.
CREATE DATABASE my_test_db1;
Output:
Example 2:
Here we will create a test database with the following parameters:
- Encoding: utf-8.
- Owner: w3wiki with postgres as the user.
- Maximum concurrent connections: 30.
CREATE DATABASE my_test_db2
WITH ENCODING='UTF8'
OWNER=w3wiki
CONNECTION LIMIT=30;
Output:
2. Using pgAdmin:
Follow the below steps to create a new database using pgAdmin.
- Step 1: Log in to PostgreSQL via pgAdmin.
- Step 2: Right click on the Databases menu and then click on New Database… sub-menu item as depicted below:
- Step 3: Now enter the new database name, owner, and configure parameters and click the OK button as depicted below:
While defining the definition, If the connection limit is set to a positive integer, it restricts the maximum number of concurrent connections that can be made to the database.
When the connection limit is set to -1, it signifies no limit on the number of connections.