PostgreSQL Database Manipulation

- Create Database

1. Using sql command CREATE DATABASE

By default, the new database will be created by cloning the standard system database template1.

Syntax

The basic syntax of CREATE DATABASE statement is as follows:

CREATE DATABASE dbname;

where dbname is the name of a database to create.

Example

Following is a simple example, which will create testdb in your PostgreSQL schema:

postgres=# CREATE DATABASE testdb;
postgres-# 

2. Using createdb Command

PostgreSQL command line executable createdb is a wrapper around the SQL command CREATE DATABASE. 

Syntax

The syntax for createdb is as shown below:

createdb [option...] [dbname [description]]

Parameters

Table below lists the parameters with their descriptions.

Parameter	Description
dbname	The name of a database to create.
description	Specifies a comment to be associated with the newly created database.
options	command-line arguments, which createdb accepts.

Options

The following table lists the command line arguments createdb accepts:

Option	Description
-D tablespace	Specifies the default tablespace for the database.
-e	Echo the commands that createdb generates and sends to the server.
-E encoding	Specifies the character encoding scheme to be used in this database.
-l locale	Specifies the locale to be used in this database.
-T template	Specifies the template database from which to build this database.
--help	Show help about createdb command line arguments, and exit.
-h host	Specifies the host name of the machine on which the server is running.
-p port	Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.
-U username	User name to connect as.
-w	Never issue a password prompt.
-W	Force createdb to prompt for a password before connecting to a database.

Open the command prompt and go to the directory where PostgreSQL is installed. Go to the bin directory and execute the following command to create a database.

createdb -h localhost -p 5432 -U postgres testdb
password ******

Above command will prompt you for password of the PostgreSQL admin user which is postgres by default so provide password and proceed to create your new dataabse.

Once a database is created using either of the above-mentioned methods, you can check it in the list of databases using \l, i.e., backslash el command as follows:

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#