{"id":2272,"date":"2025-03-07T22:20:29","date_gmt":"2025-03-07T15:20:29","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2272"},"modified":"2025-03-12T19:52:08","modified_gmt":"2025-03-12T12:52:08","slug":"postgresql-create-database","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-database-administration\/postgresql-create-database\/","title":{"rendered":"PostgreSQL CREATE DATABASE Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to create a new database using the PostgreSQL CREATE DATABASE<\/code> statement.<\/p>\n\n\n\n

PostgreSQL CREATE DATABASE statement overview #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a database is a collection of schemas<\/a>, tables, views<\/a>, indexes<\/a>, and other objects. PostgreSQL isolates each database from others, ensuring security.<\/p>\n\n\n\n

Each PostgreSQL server can have multiple databases. You can create a new database using the CREATE DATABASE<\/code> statement.<\/p>\n\n\n\n

Here’s the basic syntax of the CREATE DATABASE<\/code> statement:<\/p>\n\n\n

CREATE<\/span> DATABASE<\/span> database_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CREATE DATABASE<\/code> statement requires only the database name.<\/p>\n\n\n\n
The CREATE DATABASE<\/code> has many other options I’ll cover in the following tutorial.<\/p>\n\n\n\n
If you create a database that already exists, you’ll encounter an error.<\/p>\n\n\n\n
We’ll show you how to create a database using psql and pgAdmin tools.<\/p>\n\n\n\n
Create a new database using psql #<\/a><\/h2>\n\n\n\n
First, open a terminal on Linux or a command prompt on Windows and connect to your local PostgreSQL using the psql<\/code> command:<\/p>\n\n\n
psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll prompt you to enter the password for the postgres<\/code> user. You need enter a valid password for the postgres<\/code> user to connect.<\/p>\n\n\n\n
Second, execute the CREATE DATABASE<\/code> statement to create a new database:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> sales;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL will create a new database with the name sales<\/code>.<\/p>\n\n\n\n
If the sales<\/code> database already exists, you’ll get the following error:<\/p>\n\n\n
ERROR: database<\/span> \"sales\" already exists<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display detailed information about the sales<\/code> database by executing the following command:<\/p>\n\n\n
\\l sales<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
                                                                List of<\/span> databases\n Name<\/span>  |  Owner<\/span>   | Encoding<\/span> | Locale Provider |          Collate<\/span>           |           Ctype            | Locale | ICU Rules | Access<\/span> privileges<\/span>\n-------+----------+----------+-----------------+----------------------------+----------------------------+--------+-----------+-------------------<\/span>\n sales | postgres | UTF8     | libc            | English_United States.1252<\/span> | English_United States.1252<\/span> |        |           |<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can retrieve database names from the pg_database<\/code> view using the following query:<\/p>\n\n\n
SELECT<\/span>\n  datname\nFROM<\/span>\n  pg_database\nWHERE<\/span>\n  datname = 'sales'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 datname\n---------<\/span>\n sales<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Current Database #<\/a><\/h2>\n\n\n\n
When connecting to a PostgreSQL using psql<\/code> without specifying a database, you’re connecting to the postgres<\/code> database by default. The postgres<\/code> database becomes the current database.<\/p>\n\n\n\n
The prompt looks like this:<\/p>\n\n\n
postgres=#<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can check the current database in psql<\/code> using the \\c<\/code> command:<\/p>\n\n\n
\\c<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll return the following output:<\/p>\n\n\n
You are now connected to<\/span> the database<\/span> \"postgres\" as<\/span> user<\/span> \"postgres\".<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
After creating a new database, you can switch the current database to it using the \\c<\/code> command:<\/p>\n\n\n
\\c database_name<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following command changes the current database to the sales<\/code> database:<\/p>\n\n\n
\\c sales<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The prompt will be like the following:<\/p>\n\n\n
sales=#<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It indicates that the current database is sales<\/code>.<\/p>\n\n\n\n
Create a new database using pgAdmin #<\/a><\/h2>\n\n\n\n
If you prefer a graphical user interface (GUI), pgAdmin is an excellent tool for managing and interacting PostgreSQL databases.<\/p>\n\n\n\n
Step 1: Open pgAdmin #<\/a><\/h3>\n\n\n\n