To create a database in PostgreSQL, use the CREATE DATABASE command followed by the database name. This creates a new blank database within your PostgreSQL server.
The basic syntax is:
CREATE DATABASE database_name;
This executes immediately and creates a new database with default settings. You must have CREATEDB privilege to run this command.
You can specify additional parameters to customize the database creation:
OWNER: Sets the database owner (default is current user)TEMPLATE: Uses another database as template (default is template1)ENCODING: Sets character encoding (common values: UTF8, LATIN1)LC_COLLATE/LC_CTYPE: Sets locale settingsCONNECTION LIMIT: Sets maximum connectionsMost administrators create databases for specific applications or projects. For example:
CREATE DATABASE inventory_system
WITH
OWNER = app_user
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
CONNECTION LIMIT = 100;
This creates a database specifically configured for an inventory system application.
After creation, verify with:
\l
This lists all databases. If you encounter permission errors, check your user privileges with:
\du
According to the official PostgreSQL documentation, you may need to adjust your user permissions if you receive access denied errors.
When setting up a development environment, I created multiple databases for different projects using:
CREATE DATABASE project_a_template
WITH TEMPLATE template0;
CREATE DATABASE project_a
WITH TEMPLATE project_a_template
OWNER = dev_user;
This approach lets me maintain consistent configurations across related databases.
Many users confuse PostgreSQL databases with schemas. While both organize data, databases are completely separate entities with their own configurations and users, whereas schemas exist within a single database.