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 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.
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.
原创文章,作者:kepupublish,如若转载,请注明出处:https://blog.ytso.com/224352.html