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.

Why do you have the `if ( not exists (select ...` when your create table statement also contains `if not exists`? You could actually remove the whole `do` block completely. — a_horse_with_no_name, Apr 09 '19 at 15:54
Works for me: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=725afa04794b2c9bfa6162095a6cb213 Which SQL tool do you use to run that statement? Maybe your tool simply doesn't understand dollar quotes. — a_horse_with_no_name, Apr 09 '19 at 15:57
"[default implementation in hibernate doesn't support dollar quoting](https://stackoverflow.com/a/50380323/6854914)" (which probably makes this question a duplicate of https://stackoverflow.com/q/50237165/6854914) — , Apr 09 '19 at 16:04

J Spratt · Accepted Answer · 2021-06-01T15:19:47.530

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.

emm, I am a little bit confused, actually am doing it via java hibernate (I believe it uses JDBC(org.postgresql.Driver) (compile group: 'org.postgresql', name: 'postgresql', version: '42.1.1') driver for this) — Роман Соляник, Apr 09 '19 at 15:56

score 3 · Answer 2 · answered May 28 '21 at 09:43

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

Unterminated dollar quote

2 Answers2

Linked