[Error] Unterminated dollar quote – Liquibase

Applies to

Liquibase Secure (Pro)
Liquibase Community (Open Source)

Conditions

PostgreSQL database (or other databases supporting dollar-quoted strings)
Using SQL-based changelogs (formatted SQL, XML with SQL, JSON with SQL, or YAML with SQL)
SQL statements containing dollar-quoted string literals or procedural blocks

Issue Summary

When running Liquibase commands such as update, rollback, or validate, you encounter an "Unterminated dollar quote" error. This error occurs when Liquibase cannot properly parse SQL containing PostgreSQL dollar-quoted strings ($$ or $tag$ ). There are multiple potential root causes for this error, each requiring a different resolution.

Error Message

Reason: liquibase.exception.DatabaseException: Unterminated dollar quote started at position

Unterminated dollar quote

Root Causes

There are a few potential root causes:

Check for Missing rollbackEndDelimiter Attribute
Check for Missing endDelimiter Attribute
Check for Mismatched or Unbalanced Dollar Quotes
Check for Special Characters or Encoding Issues
Verify splitStatements Setting
Additional Debugging Steps

Resolution

Step 1: Check for Missing `rollbackEndDelimiter` Attribute

Symptoms:

Error occurs specifically during rollback operations
Your changeset uses a custom endDelimiter attribute
Your rollback SQL contains dollar-quoted strings or procedural blocks

Root Cause:

When a changeset uses the endDelimiter attribute to define a custom delimiter for the forward migration SQL, Liquibase also needs to know how to parse the rollback SQL. If the rollback statement contains SQL blocks with dollar quotes or other special delimiters, and the rollbackEndDelimiter attribute (for formatted SQL) or endDelimiter attribute (for XML/YAML/JSON rollback blocks) is not explicitly specified, Liquibase cannot properly parse the rollback SQL.

Resolution:

Add the appropriate delimiter attribute to your rollback statement:

Formatted SQL Changelogs

Add rollbackEndDelimiter to your changeset attributes:

--liquibase formatted sql

--changeset author:changesetId endDelimiter:@@ rollbackEndDelimiter:@@
DO $$
DECLARE
   r RECORD;
   alter_stmt TEXT;
BEGIN
   FOR r IN
       SELECT table_schema, table_name, column_name
       FROM information_schema.columns
       WHERE table_schema = 'schema_name'
         AND data_type = 'timestamp without time zone'
   LOOP
       alter_stmt := FORMAT(
           'ALTER TABLE %I.%I ALTER COLUMN %I TYPE timestamp with time zone;',
           r.table_schema, r.table_name, r.column_name
       );
       EXECUTE alter_stmt;
   END LOOP;
END $$;
@@

--rollback DO $$
--rollback DECLARE
--rollback    r RECORD;
--rollback    alter_stmt TEXT;
--rollback BEGIN
--rollback    FOR r IN
--rollback        SELECT table_schema, table_name, column_name
--rollback        FROM information_schema.columns
--rollback        WHERE table_schema = 'schema_name'
--rollback          AND data_type = 'timestamp with time zone'
--rollback    LOOP
--rollback        alter_stmt := FORMAT(
--rollback            'ALTER TABLE %I.%I ALTER COLUMN %I TYPE timestamp without time zone;',
--rollback            r.table_schema, r.table_name, r.column_name
--rollback        );
--rollback        EXECUTE alter_stmt;
--rollback    END LOOP;
--rollback END $$;
--rollback @@

XML Changelogs

Add endDelimiter to your <rollback> block:

<changeSet id="changesetId" author="author">
    <sql endDelimiter="@@">
        DO $$
        BEGIN
            -- SQL logic here
        END $$;
        @@
    </sql>
    <rollback endDelimiter="@@">
        DO $$
        BEGIN
            -- Rollback logic here
        END $$;
        @@
    </rollback>
</changeSet>

YAML Changelogs

Add endDelimiter to your rollback SQL block:

databaseChangeLog:
  - changeSet:
      id: changesetId
      author: author
      changes:
        - sql:
            endDelimiter: "@@"
            sql: |
              DO $$
              BEGIN
                -- SQL logic here
              END $$;
              @@
      rollback:
        - sql:
            endDelimiter: "@@"
            sql: |
              DO $$
              BEGIN
                -- Rollback logic here
              END $$;
              @@

JSON Changelogs

Add endDelimiter to your rollback SQL object:

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "changesetId",
        "author": "author",
        "changes": [
          {
            "sql": {
              "endDelimiter": "@@",
              "sql": "DO $$\nBEGIN\n  -- SQL logic here\nEND $$;\n@@"
            }
          }
        ],
        "rollback": [
          {
            "sql": {
              "endDelimiter": "@@",
              "sql": "DO $$\nBEGIN\n  -- Rollback logic here\nEND $$;\n@@"
            }
          }
        ]
      }
    }
  ]
}

Step 2: Check for Missing `endDelimiter` Attribute

Symptoms:

Error occurs during update or validate operations
Your SQL contains procedural code blocks (functions, procedures, DO blocks)
SQL contains semicolons within the procedural blocks

Root Cause:

By default, Liquibase uses a semicolon (;) as the SQL statement delimiter. When your SQL contains procedural blocks with internal semicolons (such as PostgreSQL functions or DO blocks), Liquibase incorrectly interprets these internal semicolons as statement endings, causing parsing errors with dollar-quoted strings.

Resolution:

Add the endDelimiter attribute to your changeset and use a custom delimiter:

Formatted SQL

--liquibase formatted sql

--changeset author:changesetId endDelimiter:@@
CREATE OR REPLACE FUNCTION my_function()
RETURNS void AS $$
BEGIN
    -- Function logic with semicolons
    UPDATE my_table SET status = 'active';
    INSERT INTO log_table VALUES ('Updated');
END;
$$ LANGUAGE plpgsql;
@@

XML

<changeSet id="changesetId" author="author">
    <sql endDelimiter="@@">
        CREATE OR REPLACE FUNCTION my_function()
        RETURNS void AS $$
        BEGIN
            UPDATE my_table SET status = 'active';
            INSERT INTO log_table VALUES ('Updated');
        END;
        $$ LANGUAGE plpgsql;
        @@
    </sql>
</changeSet>

YAML

databaseChangeLog:
  - changeSet:
      id: changesetId
      author: author
      changes:
        - sql:
            endDelimiter: "@@"
            sql: |
              CREATE OR REPLACE FUNCTION my_function()
              RETURNS void AS $$
              BEGIN
                  UPDATE my_table SET status = 'active';
                  INSERT INTO log_table VALUES ('Updated');
              END;
              $$ LANGUAGE plpgsql;
              @@

Step 3: Check for Mismatched or Unbalanced Dollar Quotes

Symptoms:

You've already added the appropriate endDelimiter attributes
Error persists in specific SQL statements
SQL contains nested dollar quotes or multiple dollar-quoted sections

Root Cause:

The SQL itself may contain mismatched or unbalanced dollar quote delimiters. This can happen when:

Opening and closing dollar quotes don't match (e.g., $$ opened but $body$ used to close)
Dollar quote tags are inconsistent
Comments or string concatenation interfere with dollar quote parsing

Resolution:

Review your dollar quote syntax:
- Ensure all dollar quotes are properly paired
- Use tagged dollar quotes when nesting is required
- Verify opening and closing tags match exactly

Example of incorrect syntax:

-- INCORRECT - mismatched tags
CREATE FUNCTION bad_example() RETURNS void AS $$
BEGIN
    -- logic here
END;
$body$ LANGUAGE plpgsql;  -- Wrong closing tag!

Example of correct syntax:

-- CORRECT - matching tags
CREATE FUNCTION good_example() RETURNS void AS $body$
BEGIN
    -- logic here
END;
$body$ LANGUAGE plpgsql;

For nested dollar quotes, use different tags:

CREATE FUNCTION nested_example() RETURNS void AS $outer$
BEGIN
    EXECUTE $inner$
        SELECT * FROM my_table;
    $inner$;
END;
$outer$ LANGUAGE plpgsql;

Step 4: Check for Special Characters or Encoding Issues

Symptoms:

Dollar quotes appear correct visually
Error occurs inconsistently
SQL was copied from another source or editor

Root Cause:

Hidden characters, encoding issues, or non-standard characters that look like dollar signs may be present in your SQL, preventing proper parsing.

Resolution:

Retype the dollar signs manually instead of copying them
Check file encoding - ensure your changelog file is saved as UTF-8
Look for hidden characters using a text editor that shows all characters
Verify no smart quotes or special characters were accidentally inserted

Step 5: Verify splitStatements Setting

Symptoms:

Multiple SQL statements in a single change
Some statements work, but others fail

Root Cause:

The splitStatements attribute may be interfering with dollar quote parsing, especially when set to true (the default) for SQL containing procedural blocks.

Resolution:

Set splitStatements="false" for changes containing dollar-quoted procedural code:

XML Example

<changeSet id="changesetId" author="author">
    <sql splitStatements="false" endDelimiter="@@">
        CREATE OR REPLACE FUNCTION my_function()
        RETURNS void AS $$
        BEGIN
            UPDATE my_table SET status = 'active';
        END;
        $$ LANGUAGE plpgsql;
        @@
    </sql>
</changeSet>

Formatted SQL Example

--changeset author:changesetId splitStatements:false endDelimiter:@@
CREATE OR REPLACE FUNCTION my_function()
RETURNS void AS $$
BEGIN
    UPDATE my_table SET status = 'active';
END;
$$ LANGUAGE plpgsql;
@@

Additional Debugging Steps

If none of the above steps resolve your issue:

Enable debug logging to see exactly how Liquibase is parsing your SQL:
- ```
liquibase --log-level=DEBUG update
```
Simplify your SQL to isolate the problem:
- Create a test changeset with minimal dollar-quoted SQL
- Gradually add complexity to identify what triggers the error
Test your SQL directly in PostgreSQL:
- Run the SQL directly in psql or another PostgreSQL client
- Verify it executes without errors
- Ensure the SQL syntax is valid before adding to Liquibase
Check Liquibase version:
- Some older versions of Liquibase had issues with dollar quote parsing
- Consider upgrading to the latest version if using an older release
Review the complete error stack trace:
- The full error message may provide additional context
- Note the specific line or position where parsing fails

Summary Checklist

When encountering "Unterminated dollar quote" errors, verify:

rollbackEndDelimiter is set when using custom delimiters in rollback SQL
endDelimiter is set for forward migration SQL containing procedural blocks
All dollar quotes are properly paired and use matching tags
Nested dollar quotes use different tags
No hidden characters or encoding issues exist in your SQL
splitStatements="false" is set when appropriate
SQL syntax is valid when tested directly in your database
You're using a current version of Liquibase

Applies to

Conditions

Issue Summary

Error Message

Root Causes

Resolution

Step 1: Check for Missing rollbackEndDelimiter Attribute

Symptoms:

Root Cause:

Resolution:

Step 2: Check for Missing endDelimiter Attribute

Symptoms:

Root Cause:

Resolution:

Step 3: Check for Mismatched or Unbalanced Dollar Quotes

Symptoms:

Root Cause:

Resolution:

Step 4: Check for Special Characters or Encoding Issues

Symptoms:

Root Cause:

Resolution:

Step 5: Verify splitStatements Setting

Symptoms:

Root Cause:

Resolution:

Additional Debugging Steps

Summary Checklist

Related Article(s)

Related articles

Step 1: Check for Missing `rollbackEndDelimiter` Attribute

Step 2: Check for Missing `endDelimiter` Attribute