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.

Syntax
     CREATE DATABASE db_name;

Example:

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.

Example
     CREATE DATABASE lauyou;

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

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

Parameters

ParameterDescription
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

 

Output

Create & Delete Databases

 

To select our database, use the following syntax.

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

 

Output

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.

Syntax
     DROP DATABASE [ IF EXISTS ] db_name

 

Parameter:

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

 

Example
     DROP DATABASE lauyou;

 

Output

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