PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
    • Aggregate Functions
    • Date / Time Functions
    • String Functions
    • Math Functions
Home / PostgreSQL Tutorial / PostgreSQL CREATE DATABASE

PostgreSQL CREATE DATABASE

Summary: in this tutorial, you will learn how to create new databases with various options by using the PostgreSQL CREATE DATABASE statement.

Introduction to PostgreSQL CREATE DATABASE statement

To create a new PostgreSQL database, you use CREATE DATABASE statement as shown below:

1
2
3
4
5
6
7
8
CREATE DATABASE db_name
OWNER =  role_name
TEMPLATE = template
ENCODING = encoding
LC_COLLATE = collate
LC_CTYPE = ctype
TABLESPACE = tablespace_name
CONNECTION LIMIT = max_concurrent_connection

The CREATE DATABASE statement provides you with various options when creating a new database. Let’s examine those options in more detail:

  • db_name: is the name of the new database that you want to create. The database name must be unique in the PostgreSQL database server. If you try to create a new database that has the same name as an existing database, PostgreSQL will issue an error.
  • role_name: is the role name of the user who will own the new database. PostgreSQL uses user’s role name who executes the CREATE DATABASE statement as the default role name.
  • template: is the name of the database template from which the new database creates. PostgreSQL allows you to create a database based on a template database. The template1 is the default template database.
  • encoding: specifies the character set encoding for the new database. By default, it is the encoding of the template database.
  • collate: specifies a collation for the new database. The collation specifies the sort order of strings that affect the result of the ORDER BY clause in the SELECT statement. The template database’s collation is the default collation for the new database if you don’t specify it explicitly in the LC_COLLATEparameter.
  • ctype: specifies the character classification for the new database. The ctypeaffects the categorization e.g., digit, lower and upper. The default is the character classification of the template database.
  • tablespace_name: specifies the tablespace name for the new database. The default is the template database’s tablespace.
  • max_concurrent_connection: specifies the maximum concurrent connections to the new database. The default is -1 i.e., unlimited. This feature is very useful in the shared hosting environments where you can configure the maximum concurrent connections for a particular database.

Besides the CREATE DATABASE statement, you can also use the createdbprogram to create a new database. The createdb program uses CREATE DATABASE statement behind the scenes.

PostgreSQL create database examples

The simplest way to create a new database is to use all default settings and only specify the database name as the following query:

1
CREATE DATABASE testdb1;

PostgreSQL created a new database named testdb1that has default parameters from the default template database i.e., template1.

The following statement creates a new database name hrdbwith the following parameters:

  • Encoding: utf-8.
  • Owner: hr, with the assumption that the hruser exists in the database server.
  • Maximum concurrent connections: 25.

1
2
3
4
CREATE DATABASE hrdb
WITH ENCODING='UTF8'
OWNER=hr
CONNECTION LIMIT=25;

PostgreSQL create database using pgAdmin example

Follow the steps below to create a new database via pgAdmin:

First, log in to the PostgreSQL via pgAdmin.

Second, from the Databases, right mouse click and chose the New Databases... menu item. A new window will appear.

PostgreSQL create database pgAdmin

Third, enter the new database name, owner and configure parameters. After that click OK button to create the new database.

PostgreSQL create database pgAdmin db name

PostgreSQL configure parameters

PostgreSQL create database pgAdmin SQL preview

PostgreSQL create database pgAdmin sampledb

In this tutorial, you have learned how to create new databases by using PostgreSQL CREATE DATABASE statement.

Previous Tutorial: PostgreSQL Not-Null Constraint
Next Tutorial: PostgreSQL ALTER DATABASE

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

Databases Management

  • Create New Databases
  • Modify Databases
  • Delete Databases
  • Copy a Database
  • Get Database Object Sizes

PostgreSQL Roles Administration

  • Introduction to PostgresQL Roles

PostgreSQL Backup & Restore

  • PostgreSQL Backup Databases
  • PostgreSQL Restore Databases

Tablespaces Management

  • Creating Tablespaces
  • Changing Tablespaces
  • Deleting Tablespaces

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL ANY Operator
  • PostgreSQL EXISTS
  • How To Delete Duplicate Rows in PostgreSQL
  • PostgreSQL TO_CHAR Function
  • PostgreSQL TO_NUMBER Function
  • PostgreSQL TO_TIMESTAMP Function
  • PostgreSQL CEIL Function
  • PostgreSQL MOD Function
  • PostgreSQL FLOOR Function
  • PostgreSQL ABS Function

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.