Creating a database in postgres is very easy and it takes just few seconds. create database command is used to create the database.In this tutorial we will show different option with create database command.

 

NOTE – > When we install and initialise postgres cluster, by default two template database will be created, one is template1( which is the standard system database) and template0 ( which is the secondary standard system database).
whenever we create create database command by default it will use the template of template1.template0 database should never be altered.

 

1. Creating a database with default option:( Simplest command)

postgres=# create database DBATEST;
CREATE DATABASE

2. Create a database with a specific tablespace:

postgres=# create database DBATEST with tablespace ts_postgres;
CREATE DATABASE

While mentioning tablespace name make sure the tablespace is already present in the postgres cluster:

3. Create a database with options like encoding and template

postgres#CREATE DATABASE "DBATEST"
WITH TABLESPACE ts_postgres
OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

 

— View database information from psql prompt:

postgres=# \l+

postgres=# \list+

postgres# select * from pg_database;

<< Note – alternatively database can be created using pgadmin GUI tool also >>>