PostgreSQL tutorial for Beginners – PostgreSQL – CREATE Database

PostgreSQL – CREATE Database

This chapter discusses about how to create a new database in your PostgreSQL. PostgreSQL provides two ways of creating a new database −

  • Using CREATE DATABASE, an SQL command.
  • Using createdb a command-line executable.



This command will create a database from PostgreSQL shell prompt, but you should have appropriate privilege to create a database. By default, the new database will be created by cloning the standard system database template1.


The basic syntax of CREATE DATABASE statement is as follows −


where dbname is the name of a database to create.


The following is a simple example, which will create testdb in your PostgreSQL schema

postgres=# CREATE DATABASE testdb;

Using createdb Command

PostgreSQL command line executable createdb is a wrapper around the SQL command CREATE DATABASE. The only difference between this command and SQL command CREATE DATABASE is that the former can be directly run from the command line and it allows a comment to be added into the database, all in one command.


The syntax for createdb is as shown below −

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


The table given below lists the parameters with their descriptions.

S. No. Parameter & Description
1 dbname

The name of a database to create.

2 description

Specifies a comment to be associated with the newly created database.

3 options

command-line arguments, which createdb accepts.


The following table lists the command line arguments createdb accepts −

S. No. Option & Description
1 -D tablespace

Specifies the default tablespace for the database.

2 -e

Echo the commands that createdb generates and sends to the server.

3 -E encoding

Specifies the character encoding scheme to be used in this database.

4 -l locale

Specifies the locale to be used in this database.

5 -T template

Specifies the template database from which to build this database.

6 –help

Show help about createdb command line arguments, and exit.

7 -h host

Specifies the host name of the machine on which the server is running.

8 -p port

Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.

9 -U username

User name to connect as.

10 -w

Never issue a password prompt.

11 -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 ******

The above given command will prompt you for password of the PostgreSQL admin user, which is postgres, by default. Hence, provide a password and proceed to create your new database

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)




Python Example for Beginners

Two Machine Learning Fields

There are two sides to machine learning:

  • Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
  • Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.

Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Recipes in Project-Based Learning:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

Comparing Different Machine Learning Algorithms in Python for Classification (FREE)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.  

Google –> SETScholars