How to PostgreSQL- Create & Delete Databases

In this tutorial, we will learn how to create & delete databases in PostgreSQL with various options. You can also create using pgAdmin UI.

Create PostgreSQL Database using SQL Shell

To create a new PostgreSQL database, use CREATE DATABASE statement as shown below. To create a database, you must be a superuser or have the special CREATEDB privilege.

     CREATE DATABASE db_name;


To create our database, open SQL shell (psql) from the start menu. You can check the guide to install PostgreSQL on Windows. Select default Server, database, Port, Username and enter superuser password that created while installation of PostgreSQL.


Start PostgreSQL

 postgresql create & delete database


Following is a simple example, which will create “lauyou” in your PostgreSQL schema.

     CREATE DATABASE lauyou;

The CREATE DATABASE statement provides you with various options when creating a new database.

Query Syntax
       [ [ WITH ] [ OWNER [=] user_name ]
          [ TEMPLATE [=] template ]
          [ ENCODING [=] encoding ]
          [ LC_COLLATE [=] lc_collate ]
          [ LC_CTYPE [=] lc_ctype ]
          [ TABLESPACE [=] tablespace ]
          [ CONNECTION LIMIT [=] connlimit ] ]


user_nameThe role name of the user who will own the new database, or DEFAULT to use the default. To create a database owned by another role, you must be a direct or indirect member of that role, or be a superuser.
templateThe name of the template from which to create the new database, or DEFAULT to use the default template (template1).
encodingCharacter set encoding to use in the new database.
lc_collateCollation order (LC_COLLATE) to use in the new database. This affects the sort order applied to strings,
lc_ctypeCharacter classification (LC_CTYPE) to use in the new database. This affects the categorization of characters.
tablespaceThe name of the tablespace that will be associated with the new database, or DEFAULT to use the template database's tablespace.
connlimitHow many concurrent connections can be made to this database. -1 (the default) means no limit.


Select PostgreSQL Database

You can check available database list using \l (slash el).

Database List
     postgres-# \l



Create & Delete Databases


To select our database, use the following syntax.

Select Database Query
    postgres-# \c db_name;
    postgres-# \c lauyou;



Create & Delete Databases


Drop Database

The drops a database syntax will remove the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner.

     DROP DATABASE [ IF EXISTS ] db_name



IF EXISTS: Do not throw an error if the database does not exist. A notice is issued in this case.


     DROP DATABASE lauyou;



Create & Delete Databases

DROP DATABASE cannot be executed inside a transaction block. This command cannot be executed while connected to the target database.

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...