intro
Let's learn everything you need to know about CREATE DATABASE in PostgreSQL, the statement you can employ to create new databases in your Postgres instance.
Adding databases to a new database server is one of the most common tasks performed by DBAs or database users. While standard SQL does not provide a specific command for this, there are several options available, with the CREATE DATABASE command being the most common. Follow this guide to master CREATE DATABASE in PostgreSQL.
Let dive in!
What Is CREATE DATABASE in PostgreSQL?
In PostgreSQL, CREATE DATABASE is the statement used to create new databases. Keep in mind that—unlike standard SQL, where a database is typically a collection of schemas within a single catalog—PostgreSQL treats each database as a separate entity.
In particular, each Postgres database has its own catalog and operates independently. This means that schemas, tables, and other objects are confined to their respective databases, and you cannot directly access objects across different databases. Thus, cross-database queries are forbidden in PostgreSQL. To achieve similar functionality, you can use multiple schemas within the same database.
Note: CREATE DATABASE is not part of the SQL standard, which means database creation operations in SQL are implementation-specific.
Postgres CREATE DATABASE Syntax and Usage
This is the basic syntax to use CREATE DATABASE in PostgreSQL:
1
CREATE DATABASE db_name;
Where db_name is the name of the new Postgres database you want to create.
A more complete syntax for the Postgres CREATE DATABASE command includes a few optional parameters:
1
CREATE DATABASE db_name
2
[ WITH ] [ OWNER [=] user_name ]
3
[ TEMPLATE [=] template ]
4
[ ENCODING [=] encoding ]
5
[ STRATEGY [=] strategy ]
6
[ LOCALE [=] locale ];
Where:
Remember that other parameters are available. Discover them all through the official documentation.
Notes:
How to Create a Database in PostgreSQL
In the following sections, you will learn how to create a database in PostgreSQL using the CREATE DATABASE command or a visual database client.
With CREATE DATABASE
Connect to your PostgreSQL server and run the following query to create a new database called company with the default options:
1
CREATE DATABASE company;
Similarly, you can create a database called ecommerce with a specific locale, encoding, and template using the following query:
1
CREATE DATABASE ecommerce
2
LOCALE 'sv_SE.iso885915'
3
ENCODING LATIN9
4
TEMPLATE template0;
If you are wondering how to see the output of these commands, follow our guide on how to list databases in PostgreSQL.
With a Visual Database Client
The procedure outlined above works well, but not having visual feedback on what you are doing can make everything more complex. To combat this, run a Postgres CREATE DATABASE query in a visual database client like DbVisualizer:
That makes everything easier because you can clearly see that the query was executed successfully and that a new database was added to the server.
But what if you want to create a database without manually running a query, with just with a few clicks? In DbVisualizer, that is possible too!
Download DbVisualizer for free, connect to your PostgreSQL server, and navigate to the “Databases” tab on the right:
Expand the dropdown, right-click on "Database," and select the “Create Database…” option:
The following modal will appear:
Here, you can create a new database by simply filling out the form and clicking the "Execute" button:
As you can see, the generated query is also shown in a dedicated text area! After clicking "Execute," confirm the creation of the database, and it will appear immediately with a special icon in the database list:
Note:
This database creation feature is available only in DbVisualizer Pro, which offers many other powerful features. Grab a 21-day free trial today!
Conclusion
In this article, you learned how to use CREATE DATABASE in PostgreSQL by exploring its syntax and seeing it in action through a couple of examples.. While you can run that query directly from the command line using psql, database management becomes much easier with a powerful, feature-rich database client like DbVisualizer, which offers full support for PostgreSQL and many other databases.
FAQ
What is the Postgres psql create database command?
The Postgres psql create database command on Linux is:
1
sudo -u postgres psql -c 'CREATE DATABASE db_name;'
The psql command connects to PostgreSQL as the postgres user. Then, it uses the -c flag to runs the CREATE DATABASE command directly.
Then, grant all permissions to the database with:
1
sudo -u postgres psql -c 'GRANT ALL PRIVILEGES on DATABASE db_name to user_name;'
This assigns full permissions to user_name, allowing them to manage the database. Note that both commands require superuser privileges, hence sudo -u postgres .
How to create a PostgreSQL database from the bash?
To create a PostgreSQL database from the bash shell without using psql, run the createdb command on your Linux PostgreSQL server:
1
sudo -u postgres createdb db_name
This creates db_name with default settings as the postgres user.
What are the privileges required to launch CREATE DATABASE in PostgreSQL?
To run CREATE DATABASE in PostgreSQL, you must be a superuser or have the CREATEDB privilege. To grant the CREATEDB privilege, use:
1
ALTER ROLE user_name CREATEDB;
This allows user_name to create databases without full superuser rights.
How to create a database schema in PostgreSQL?
In PostgreSQL, schemas organize database objects within a database. To create a new schema, connect to the database and run the following CREATE SCHEMA query:
1
CREATE SCHEMA schema_name;
Why use a visual database client to create a database in Postgres?
Using a visual database client like DbVisualizer to create a database in Postgres offers several advantages. It provides an intuitive, user-friendly interface, making database creation and management much easier compared to using command-line tools. With DbVisualizer, you can visually see your queries, monitor execution, and access powerful features like drag-and-drop query creation and query optimization. Test everything it has to offer today!
