Facing Unterminated dollar quote started at position 3 in SQL DO $$ for postgresql migration

General Discussion
1

Hello,

I am struggling with an unterminated dollar quote error when I did a postgresql migration

The SQL is very simple , The below runs well when I connect to Postgresql with UI tool , however when I put in it liquibase I got unterminated dollar quote error. Per the stack trace the error was thrown by postgresql , since it works well on UI I am wondering if liquibase chunk the SQL at the first ; instead of sending everything between $$?

DO $$
DECLARE
var_timeseries_id BIGINT ;
var_constituent_timeseries_id BIGINT ;
BEGIN

END $$;

Caused by: liquibase.exception.DatabaseException: Unterminated dollar quote started at position 3 in SQL DO $$
DECLARE
var_timeseries_id BIGINT. Expected terminating $$ [Failed SQL: (0) DO $$
DECLARE
var_timeseries_id BIGINT]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:445)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:160)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1270)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1252)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:702)
… 56 common frames omitted
Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 3 in SQL DO $$
DECLARE
var_timeseries_id BIGINT. Expected terminating $$
at org.postgresql.core.Parser.checkParsePosition(Parser.java:1443)
at org.postgresql.core.Parser.parseSql(Parser.java:1342)
at org.postgresql.core.Parser.replaceProcessing(Parser.java:1294)
at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43)
at org.postgresql.core.QueryExecutorBase.createQueryByKey(QueryExecutorBase.java:369)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:313)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:441)
… 61 common frames omitted

2

The default statement delimiter in Liquibase is the semicolon. If you want $$ to be your statement delimiter, you need to set the “endDelimiter” for the changeset.

3

Did this solution work?

4

I came here with the same question. No, the above answer is not useful. The $$ symbol is not being used as a statement delimiter. It is used instead of a quotation mark. See dollar-quoted strings. It looks like a bug in Liquibase in the sense that it doesn’t recognise the $$ symbol as a means of quoting a string literal, so it looks at a ; in the middle of the string literal and thinks it terminates a statement. There are workarounds such as changing the endDelimiter from a ; to something else.

That might work for some, but if you have many other statements in the same changeset, this workaround is not ideal.

5

We addressed this issue by implementing a SQL file change set. This approach bypasses the interpreter and sends the file directly to the RDBMS for parsing.

  • changeSet:
    id: CREATE
    author: dba
    comment: CREATE Script
    changes:
    • sqlFile:
      dbms: ‘postgresql’
      splitStatements: false
      path: src/main/resources/db-scripts/app1/v2/ddl.sql
1 Like