Create a Database in PostgreSQL

In this article, we are going to create a database in PostgreSQL with the help of the example.PostgreSQL Provides a CREATE DATABASE statement to create a new database. Let see how can we create a new PostgreSQL database using an example.

Introduction to PostgreSQL CREATE DATABASE Statement

PostgreSQL provides a statement name CREATE DATABASE which is used to create a new database. CREATE DATABASE comes with many features that allow us to create a new PostgreSQL database.
To create a new database you must have super user privileges or you must have CREATEDB privilege.

Syntax

This is the complete syntax to create a database in PostgreSQL.

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

Parameters

CREATE DATABASE support multiple parameters, we will discuss all the parameter one by one.

• name:- The name of the database you want to create.
• user_name:- The role name of the use who will own the newly created database or defaulse to use the defaut user ( the user executing the command ).To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.
• template:- The name of the template from which to create new database or default to use the default template ( template1 ).
• encoding:- Character set encoding to use in new database.
• lc_collate:- Collation order to use in new database.
• lc_ctype:- Character classification to use in new database.
• tablespace_name:- The name of the tablespace that will associated with new database.
• allowconn:- If false than no one can connect to this database.The default it True.
• connlimit:- How many concurrent connection made to this database, The default is no limit.
• istemplate:- If True, then this database can be cloned by anu user with CREATEDB privilege.

Now it’s time to come create a database in PostgreSQL.

Create a Database in PostgreSQL

You have to log in with superuser privilege or a user with CREATEDB privilege.

Examples:

To create a new PostgreSQL database.

CREATE DATABASE programmingfunda;

Create a database programmingfunda owned by the user codingapp with the default tablespace pfspace.

CREATE DATABASE programmingfunda OWNER codingapp TABLESPACE pfspace;

If you are using the psql client tool then you see all the databases by typing the \l command.

\l

Output

 demodb           | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 movtek           | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 postgres         | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 programmingfunda | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

Create Database with Some Parameters

The following CREATE DATABASE statement is used to create codingdb database with some parameters.

Example: Create PostgreSQL Database

CREATE DATABASE codingdb WITH ENCODING 'UTF-8' OWNER codingapp CONNECTION LIMIT 100;

In the above example, we have created a PostgreSQL database codingdb with encoding UTF-8, the owner is codingapp and the number of concurrent connections to the database is 100.

Frequently Asked Questions

---

Conclusion

So in this PostgreSQL Tutorial, you have seen everything about create a database in PostgreSQL with the help of examples. Using CREATE DATABASE statement and its parameters you can create your Postgres database easily.

I hope this article is very helpful for you. If you like this article, please share and keep visiting for further PostgreSQL tutorials.

Reference:- Click Here
More about PostgreSQL:- Click Here

Thanks for your valuable time ….