CODEWITHSUNDEEP
postgresql
Wayne
Wayne

Reputation: 3519

How to initialise PostgreSQL instance by creating database and tables

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

Upvotes: 2

Views: 386

Answers (1)

Iakovos Belonias
Iakovos Belonias

Reputation: 1373

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

Upvotes: 2

Related Questions