Menu Close

Create a Database in PostgreSQL

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


Which statement is used to create PostgreSQL Database?

Ans:- PostgreSQL gives a CREATE DATABASE statement to create a new database.

How do I list databases in PostgreSQL?

Ans:- Use \l or \l+ to show all the databases in the PostgreSQL server.
Use SELECT statement to query the database from pg_database.

How do I switch databases in PostgreSQL?

Ans:- To switch into the database, firstly you have to login into the PostgreSQL server and use \c databasename to switch the databases.

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 ….

PostgreSQL Tutorial 2022
Drop a Database in PostgreSQL

Related Posts