Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save r37r0m0d3l/ec998e9fdb89dab69a0d6f7463e599cd to your computer and use it in GitHub Desktop.
Save r37r0m0d3l/ec998e9fdb89dab69a0d6f7463e599cd to your computer and use it in GitHub Desktop.
Create Types and Roles If Not Exist in PostgreSQL
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment