Postgresql Language

Create Database

SELECT FORMAT(
	'CREATE DATABASE %I WITH OWNER %I ENCODING ''UTF8'' LOCAL_PROVIDER = ''libc'' CONNECTION LIMIT -1 IS_TEMPLATE = false',
	'my_database',
	'db_user'
)
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'd2data')
\gexec

\c d2data

CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

Being able to rerun your queries during development is always a plus, and using psql we can do this. Because in PostgreSQL you can't use an IF NOT EXISTS clause in a CREATE DATABASE command.

Warning

You cannot create a database inside a DO block;

DO
$$
    DECLARE
        -- Variables here
    BEGIN
        -- Logic Here
    END
$$

This is because the command CREATE DATBASE cannot run inside a transaction.

If you just need a one-off, and just want to create a database: Here ya go!

CREATE DATABASE my_database WITH OWNER db_user 
    ENCODING ''UTF8'' 
    LOCAL_PROVIDER = ''libc'' 
    CONNECTION LIMIT -1 
    IS_TEMPLATE = false

If you are not a superuser, then in order to have the ability to create a database, you will need the CREATEDB privilege.

CREATE ROLE db_creator
    LOGIN
    PASSWORD 'some_secure_password'
    CREATEDB;

We are not going to get into Roles right here but the LOGIN and PASSWORD privileges are optional, you could replace those with a NOLOGIN privilege and delegate this ability to a group role. If you want to read more about Roles, checkout PostgreSQL Roles