Unterminated dollar quote

How to use IF statement in the PostgreSql (11 version)? I tried just raw IF usage but got problem (syntax error at or near “IF”). To resolve this problem people propose to use ‘do &&’ but it does not work as well (Unterminated dollar quote started at position 3 in SQL DO $$ BEGIN IF ……). Here is my SQL code:

DO $$
BEGIN
  IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'categories')) THEN
    CREATE TABLE IF NOT EXISTS categories
    (
      id   SERIAL NOT NULL,
      name character varying(40),
      CONSTRAINT categories_pkey PRIMARY KEY (id)
    );

    INSERT INTO categories (name) VALUES ('Games');
    INSERT INTO categories (name) VALUES ('Multimedia');
    INSERT INTO categories (name) VALUES ('Productivity');
    INSERT INTO categories (name) VALUES ('Tools');
    INSERT INTO categories (name) VALUES ('Health');
    INSERT INTO categories (name) VALUES ('Lifestyle');
    INSERT INTO categories (name) VALUES ('Other');
  END IF;
END
$$;

All I need is to create table and insert some init data to it if table does not exist.

Answer

Some platforms do not support dollar quoting. In your specific example you should have a semicolon after the last END. You may need to add a DECLARE statement also.

DO
$$
DECLARE
BEGIN
    IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'categories')) 
    THEN
        CREATE TABLE IF NOT EXISTS categories
        (
          id   SERIAL NOT NULL,
          name character varying(40),
          CONSTRAINT categories_pkey PRIMARY KEY (id)
        );

        INSERT INTO categories (name) VALUES ('Games');
        INSERT INTO categories (name) VALUES ('Multimedia');
        INSERT INTO categories (name) VALUES ('Productivity');
        INSERT INTO categories (name) VALUES ('Tools');
        INSERT INTO categories (name) VALUES ('Health');
        INSERT INTO categories (name) VALUES ('Lifestyle');
        INSERT INTO categories (name) VALUES ('Other');
    END IF;
END;
$$  LANGUAGE PLPGSQL;

For platforms that don’t recognize dollar quoting you can use ' instead. You’ll need to escape any ' in the body of the anonymous function though.

Like so:

DO
'
DECLARE
BEGIN
    IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''categories'')) 
    THEN
        CREATE TABLE IF NOT EXISTS categories
        (
          id   SERIAL NOT NULL,
          name character varying(40),
          CONSTRAINT categories_pkey PRIMARY KEY (id)
        );

        INSERT INTO categories (name) VALUES (''Games'');
        INSERT INTO categories (name) VALUES (''Multimedia'');
        INSERT INTO categories (name) VALUES (''Productivity'');
        INSERT INTO categories (name) VALUES (''Tools'');
        INSERT INTO categories (name) VALUES (''Health'');
        INSERT INTO categories (name) VALUES (''Lifestyle'');
        INSERT INTO categories (name) VALUES (''Other'');
    END IF;
END;
'  LANGUAGE PLPGSQL;

DBFiddle to view a working example.