2

I need to create a new database using PostgreSQL and need to initialise the "database" by creating the database and the tables.

I was thinking of creating a init.sql file and using the file together with Docker when initialising the docker image.

I need an example which takes care of initialising(creating schemas and tables conditionally based on schema versions) the database.

I have the following pseudo example and would like to see a real Postgres example.

pseudo example:

1) create database if it does not exist
2) set or increment database design version

// create version 0 tables
3) create tables if it does not exist

// create version 1 tables
4) create future version tables and apply table alterations

Wayne
  • 3,359
  • 3
  • 30
  • 50
  • You might benefit from using a database migration tool such as *Flyway* or *Liquibase* after initialing creating the database. These tools run scripts to define and populate your schema, tables, and data. These tools can also clear the content of the database for reuse in testing so you need not recreate a new empty database, – Basil Bourque Mar 15 '19 at 06:45
  • I second Basil's recommendation use a schema migration tool. I am fan of Liquibase but Flyway or e.g. [Sqitch](https://sqitch.org/) or just as good –  Mar 15 '19 at 07:19

1 Answers1

2

CREATE DATABASE can only be executed as a single statement. So it cannot be run inside a function or DO statement.

You need

And

DO
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_database WHERE datname = 'mydb') THEN
      RAISE NOTICE 'Database already exists'; 
   ELSE
      PERFORM dblink_exec('dbname=' || current_database()  -- current db
                        , 'CREATE DATABASE mydb');
   END IF;
END
$do$;

A detailed explanation

Iakovos Belonias
  • 1,217
  • 9
  • 25
  • That script will only work if the login requires no password. – Laurenz Albe Mar 15 '19 at 06:44
  • The script only explains how to create a database, the creation of the schema and the creation of the tables are not detailed in this answer yet. My preferred example would be something that conditionally creates: database, schemas and tables AND caters for schema versioning. This links shows how it is done for mySQL: https://database.guide/how-to-create-a-database-from-a-script-in-mysql/ – Wayne Mar 17 '19 at 23:11
  • Yes you are right, I didn't realise it, I was trying to enrich my answer – Iakovos Belonias Mar 18 '19 at 08:37
  • 1
    @Wayne: that link is useless for Postgres –  Mar 18 '19 at 09:00