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. — , 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. — , 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
Both your answers don't work for me, and i still get the same error — hidden_machine, Sep 10 '22 at 11:24
@hidden_machine The second snippet in this answer completely removes dollar quoting so I'm not sure how you'd get "Unterminated dollar quote" while using that. Can you elaborate? — J Spratt, Sep 12 '22 at 14:18
@JSpratt My problem was more related to SpringUtils, the problem being that ";" was the default statement separator, changing it to something else according to this [answer](https://stackoverflow.com/questions/57850511/spring-scriptutils-unterminated-dollar-quote-in-postgresql) solved the problem. — hidden_machine, Sep 14 '22 at 15:22

score 4 · 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

score 0 · Answer 3 · answered Nov 08 '22 at 18:42

I'm posting this here as this is the first result on Google when searching for this problem on Delphi's FireDAC.

My issue was that the function had comments with accented characters, and after removing those comments, the "Unterminated dollar quote" error went away. So be careful with UTF8 characters in comments.

I would also like to note that I've had similar problems with operators that use special characters, like the !~ (NOT SIMILAR TO) operator, which was wrongfully sent to PostgreSQL as the ~ (SIMILAR TO) operator.

Unterminated dollar quote

3 Answers3

Linked