How to Create a Database PostgreSQL

Intro

In PostgreSQL you will often start by creating and selecting which database to use. You need a database to store data, so this is a logically place to start. In this article, we will learn how to create databases in PostgreSQL.

Getting Setup

For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called docker-compose.yml

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

We can run this file, we can use docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.

You can then login by leaving the host empty and using the following credentials.

POSTGRES_USER: username
POSTGRES_PASSWORD: password

The Basics

To create a database, we can use the CREATE DATABASE statement. For example, let’s create a database called "sakila" named after the example database we will be using for many tutorial (insert link).

CREATE DATABASE sakila;

In PostgreSQL, you will get a schema called public by default. To select the database you want to use, you will need to connect directly to the database or select the database in PhpMyAdmin.

Create if Not Exists

PostgreSQL doesn't seem to have a IF NOT EXISTS clause similar to mysql, however, we can still do something similar with a more complex query.

SELECT 'CREATE DATABASE sakila'
  WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'sakila')

In the above query, we only run the create database script if we can not find a database in the PostgreSQL special table pg_database.