How to create a database in PostgreSQL

What is PostgreSQL?

PostgreSQL is a well-known open source relational database. It aims to manage data without a size limit and protects it by providing a fault-tolerant environment. It provides numerous features that make it one of the most powerful database managers.

Creating a database in PostgreSQL

The first step is to create a database.

Basic syntax

CREATE DATABASE testname;

The create command creates a database with a name, which is referred to here as testname.

Complete syntax

CREATE DATABASE db_name
OWNER =  owner_name
TEMPLATE = template_name			
ENCODING = encoding_type			
LC_COLLATE = lccollate			
LC_CTYPE = lcctype
TABLESPACE = tablespace_name
CONNECTION LIMIT = max_connection_limit

Parameters

name: This specifies the name of the database that needs to be created.

owner: This specifies the user that owns the database.

template: This specifies the type of template you are using for your database.

encoding: This specifies the type of character set encoding you are using for your database.

lc_collate: This specifies the order in which the strings of your database are going to be sorted.

lc_ctype: This specifies the way you are going to classify the characters in your database.

tablespace: This specifies the name of the tablespace that you are going to use in your database.

connection limit: This specifies the maximum number of connections you can have concurrently in your database.

Scenarios where creating a database can fail

If the output has no response, it means that your database has been created successfully. But, there can be a scenario where you get an output such as:

command not found

If this happens, it can either mean that your PostgreSQL was not installed properly, in which case you can re-install, or the correct path was not set in the shell script. You can then try creating a database by providing an absolute path, such as:

$ /c/user/programs/postgresql/bin/createdb mydb

Note that the above mentioned path is just an example, and the path at your machine will be different. You can check the installation instructions to find out the absolute path.

You can also come across another output related to the server such as:

file not found

or

no such file or directory

or

could not connect to server

Again, you need to thoroughly read the installation guide and see whether you are following all the steps that start the server. You can also check if there is any missing file that needs to be installed.

Another thing is to make sure that you are creating a database through an account that has permissions to create the database. If you are unsure if you have those rights, you can consult the administrator and ensure your creating rights privilege. Otherwise, the following error may occur:

permission denied to create database

Dropping the database

Now, after successfully creating a database and manipulating it according to your requirements, you might want to drop it. Here is the command for dropping the database:

DROP DATABASE testname