Create, Select, and Drop a Database in PostgreSQL

Create a Database in Postgresql

Using CREATE DATABASE

The CREATE DATABASE command is used for database creation using the PostgreSQL shell prompt, but the database can only be created when you have the right privileges for database creation. By default, the _template1_ standard system database is cloned for the creation of the new database.

Syntax:

The syntax for creating a database in Postgresql is given below:

Parameters:

Below is the list given for all the parameters used in the createdb command with their descriptions.

Example:

Below is a command to create the database with the name exampledb.

Output:

Using "createdb" Command

The createdb command is used for creating a database and it is a wrapper for the CREATE DATABASE command.

Syntax:

The syntax for using createdb for creating a database is given below:

Parameters:

Below is the list given for all the parameters used in the createdb command with their descriptions.

Options:

Createdb accepts the given below command line arguments:

Example:

1. Open the command prompt at the path C:\Program Files\PostgreSQL\15\bin.

2. Then type the following command to create the database.

This command will ask for the password of the “postgres” user. Type the password and then press enter, after that database with ex1 will be created.

We can verify whether the command has successfully created a database or not by running the \l command from psql.

PostgreSQL Drop Database

We can use the SQL DROP command for deleting a database in PostgreSQL.

The syntax for the drop database is given below:

When this command is executed then the directory having the information of the database is removed and the catalog entries are also removed. DROP DATABASE can only be executed by the owner of the database. This command will not execute if the database to be deleted is in use by someone

Example:

This command will delete the database with the name db.

Output:

IF Exists:

IF Exists can be used with all versions which support the DROP DATABASE.

Syntax of using IF EXISTS with DROP DATABASE is given below:

This command first checks whether the database you want to delete will exist or not. The database will be dropped if it exists. It prints the informative notice message on the command prompt if the database does not exist.

Follow the below given to test the working of IF EXISTS

• Create a database with the exampleDB by writing the command given below:

• Now write the command to drop the database with IF EXISTS.

  Output:

  

  If the database exists then the result of DROP DATABASE with IF EXISTS is similar to the DROP DATABASE command.

• Now run the DROP DATABASE with IF EXISTS again when the database does not exist.

  Output:

  

  A message is displayed on the command prompt with the message that the database does not exist.

• Now run the DROP DATABASE command again without IF EXISTS.

  Output:

  

  DROP DATABASE without IF EXISTS throws an error if the database does not exist.

PostgreSQL Select Database

Database SQL Prompt

Let us assume the PostgreSQL client was successfully launched by us.

• Run the command given below to display all the available databases.

  After running the above command, a list of all the databases is displayed as shown below:

  

• Now type the command given below to connect to a particular database or to select a particular database.

• Now we are connected with the database named exampleDB.

  

Select the Database Using "pgAdmin"

We can also select by the pgAdmin. Follow the steps provided below to select the database with the help of pgAdmin.

Step - 1:

Open the UI of pgAdmin.

Step - 2:

Then click on the database to select the database as shown in the below figure.

Step - 3:

Now click on the tools option then the drop-down will appear, then select the query tool option available in the dropdown.

Step - 4

Now the window will open on the screen in which a query window will open and a connection will be created with the database we have selected. Here we can execute the queries.