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.
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
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
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.
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