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;

I had some issues with that, the statement IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') always was False, so I changed to this:

DO
$$
BEGIN
	CREATE ROLE reader LOGIN PASSWORD 'testPass';
	EXCEPTION WHEN DUPLICATE_OBJECT THEN
		RAISE NOTICE 'reader  exists, skipping...';
END
$$;

I hope this helps :)

how about while the enum type already exists?

example :

CREATE TYPE IF NOT EXISTS task_status AS ENUM  ('todo', 'doing', 'blocked', 'done');

but, I got the wrong syntax in that way?

how about while the enum type already exists?

example :

CREATE TYPE IF NOT EXISTS task_status AS ENUM  ('todo', 'doing', 'blocked', 'done');

but, I got the wrong syntax in that way?

Pls do you get an answer to this?

Yes, I check

👍

I would also be interested in the correct syntax to CREATE TYPE IF NOT EXISTS :)

The current best solution I've found is

do $$
begin
if not exists (select 1 from pg_type where typname = 'name_of_your_type') then
   -- create type
end if;
end $$;

DO ' BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = ''name_of_your_type'') THEN
        CREATE TYPE name_of_your_type AS ENUM (''aaaa'', ''bbbb'', ''cccc'');
    END IF;
END ';

In some situation the ' better than $$, such as the situation in Java

Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 3 in SQL DO $$...