Unterminated dollar-quoted string at or near "$$

Question

I'm trying to declare some variables using DBeaver and keep hitting this error.

Unterminated dollar-quoted string at or near "$$

 DO $$
 DECLARE A integer; B integer;

BEGIN   
END$$;

Any ideas?

Bernardabernardi

postgresql.org/docs/current/static/sql-do.html and postgresql.org/docs/9.4/static/… – Groom

Answer 1

DBeaver was the issue. Switched to PGAdmin and no more problems.

Bernardabernardi

Answer 2

As of DBeaver 6, you can execute the script with ALT-X (on Windows), which does not attempt to do variable capture/interpolation involving dollar signs.

Frock

According to the icons in the editor, alt+x executes the whole script. This is the easier solution, but you can also highlight the entire query and execute the single statement and it won't give any errors. – Sportswoman

Answer 3

The syntax posted is fine. Your problem is that the client application or driver is mangling the query, probably because it doesn't understand dollar-quoting.

It might be trying to split it into separate statements on semicolons, running:

• DO $$ DECLARE A integer;
• B integer;
• BEGIN END$$;

as three separate statements. This would result in the reported error, e.g.

$ psql -c 'DO $$ DECLARE A integer;'
ERROR:  unterminated dollar-quoted string at or near "$$ DECLARE A integer;"
LINE 1: DO $$ DECLARE A integer;
           ^

This is why you must specify your client driver/application when asking questions.

---

Another possibility with some clients is that it might treat $ as an escaped query-parameter placeholder and replace it with a single $ or try to substitute it for a server-side placeholder like $1. That's not what's happening here, though.

Prepare

Answer 4

DBeaver also gives this error when there is a SQL syntax error in the script. In my case, it was a pair of mismatched parenthesis in a select calculated column.

Dufresne