How to initialise PostgreSQL instance by creating database and tables

Question

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

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
– Basil Bourque, Commented Mar 15, 2019 at 6:45
I second Basil's recommendation use a schema migration tool. I am fan of Liquibase but Flyway or e.g. Sqitch or just as good — user330315
– user330315, Commented Mar 15, 2019 at 7:19

Iakovos Belonias · Accepted Answer · 2019-03-18 08:36:46Z

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

edited Mar 18, 2019 at 8:36

answered Mar 15, 2019 at 6:34

Iakovos Belonias

1,38310 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Laurenz Albe Over a year ago

That script will only work if the login requires no password.

Wayne Over a year ago

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: database.guide/how-to-create-a-database-from-a-script-in-mysql

Iakovos Belonias Over a year ago

Yes you are right, I didn't realise it, I was trying to enrich my answer

user330315 Over a year ago

@Wayne: that link is useless for Postgres

Collectives™ on Stack Overflow

How to initialise PostgreSQL instance by creating database and tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related