Unterminated dollar quote

Question

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 1

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.

Answer 2

I have the same issue in play framework evolution, fixed by adding additional semicolon

$BODY$
BEGIN
    if NEW.year_after IS NULL THEN

        NEW.year_after := 100;;
        NEW.after := 200;;
    end if;;
    RETURN NEW;;
END;;
$BODY$ language plpgsql