Create Types and Roles If Not Exist in PostgreSQL

types_and_roles_demo.sql

BEGIN;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
        create type task_status AS ENUM ('todo', 'doing', 'blocked', 'done');
    END IF;
END
$$;
CREATE TABLE IF NOT EXISTS
tasks (
  id           integer PRIMARY KEY,
  title        varchar(200),
  status       task_status NOT NULL DEFAULT 'todo',
  created_date timestamp
);

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN
        CREATE ROLE reader;
    END IF;
END
$$;
GRANT SELECT ON tasks TO reader;

COMMIT;