In this short tutorial we will learn how to create database in PostgreSQL database server. Creating database is a main task of a person who wants to store, update, delete data in PostgreSQL database engine. The tutorial is intended especially for beginners who want to start learning to use the PostgreSQL database which will later be used in real projects.
Creating Database in PostgreSQL
To be aware of beforehand, by default, PostgreSQL provides two ways of creating a new database, namely :
- Using SQL command : CREATE DATABASE.
- Using executable command-line executable : createdb.
The two commands above have the same function, the only difference between them is the command-line can be directly run from the command line prompt on OS shell and it allows a comment to be added into the database, all in one command. Detailed explanation of creating database in PostgreSQL will be explained in the sub-chapter below.
1. Using SQL command : CREATE DATABASE
The CREATE DATABASE command will create a new database on PostgreSQL and done via PostgreSQL shell propt. To be able to run this command we must have the appropriate privilege to create a database. By default, the new database will be created by cloning the standard system database template1.
Syntax
The basic syntax for creating database is :
CREATE DATABASE dbname;
The full syntax is as follow (following with several paramters) :
CREATE DATABASE name
[ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ STRATEGY [=] strategy ] ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ ICU_LOCALE [=] icu_locale ]
[ LOCALE_PROVIDER [=] locale_provider ]
[ COLLATION_VERSION = collation_version ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ]
[ OID [=] oid ]
Example
Currently we have database list as shown below :
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
We will create a new database with a simple step as follow :
postgres=# CREATE DATABASE dbtest01; CREATE DATABASE
A new database will be exist on database list, as shown below :
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbtest01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
2. Using executable command-line executable : createdb
Membuat database baru pada PostgreSQL dengan perintah creatdb dapat dilakukan langsung dari operatins system shell tanpa harus login terlebih dahulu ke PSQL shell. Using creatdb command line, will allow to add a comment into the database, all in one command. Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the -O option, if the executing user has appropriate privileges.
Systax
The syntax for createdb command line is as shown below :
createdb [option...] [dbname [description]]
Parameters :
| 1 | options command-line arguments, which createdb accepts. |
| 2 | dbname The name of a database to create. |
| 3 | description Specifies a comment to be associated with the newly created database. |
Options :
| No. | Option & Description |
|---|---|
| 1 | -D tablespace Specifies the default tablespace for the database. |
| 2 | -e Echo the commands that createdb generates and sends to the server. |
| 3 | -E encoding Specifies the character encoding scheme to be used in this database. |
| 4 | -l locale Specifies the locale to be used in this database. |
| 5 | -T template Specifies the template database from which to build this database. |
| 6 | –help Show help about createdb command line arguments, and exit. |
| 7 | -h host Specifies the host name of the machine on which the server is running. |
| 8 | -p port Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections. |
| 9 | -U username User name to connect as. |
| 10 | -w Never issue a password prompt. |
| 11 | -W Force createdb to prompt for a password before connecting to a database. |
Example
We will create a new database called as testdb01.
ramans@infodiginet:~$ createdb -h localhost -p 5432 -U postgres testdb02 Password:
Then we verifty a new database which was created.
amans@infodiginet:~$ sudo su - postgres
[sudo] password for ramans:
postgres@infodiginet:~$ psql
psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbtest01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(5 rows)
Conclusion
In this tutorial we have learned how to create a new database in PostgreSQL database server. There are two options to create database, namely : using SQL command : CREATE DATABASE. and using executable command-line executable : createdb.
