How to fix Unterminated dollar-quoted string in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 26, 2025· 277 views

The PostgreSQL "Unterminated dollar-quoted string" error occurs when a dollar-quoted string ($$...$$) is not properly closed, typically due to SQL client tools not understanding dollar quoting syntax and incorrectly splitting statements at internal semicolons. Dollar quoting is a PostgreSQL feature for writing string constants without escaping nested quotes, but many tools lack proper support for it.

Quick fix

Fixes Unterminated dollar-quoted string

# Using psql
psql -U username -d database_name -c "CREATE OR REPLACE FUNCTION my_func() RETURNS void AS $$
BEGIN
  SELECT 1;
END;
$$ LANGUAGE plpgsql;"
  1. Test with psql or pgAdmin first
  2. Use the tool's "raw query" or "batch file" mode
  3. Save SQL in a .sql file and execute with psql
  4. Configure migration tools with correct end delimiters
postgresql — error
# Using psql
psql -U username -d database_name -c "CREATE OR REPLACE FUNCTION my_func() RETURNS void AS $$
BEGIN
  SELECT 1;
END;
$$ LANGUAGE plpgsql;"
Unterminated dollar-quoted string

What this error means

PostgreSQL uses dollar quoting ($$...$$) as an alternative to single-quoted strings, particularly useful in function definitions and procedural code. Dollar-quoted strings consist of an opening `$$` (or `$tag$` for named quotes), arbitrary content, and a closing `$$` (or `$tag$`). The error "Unterminated dollar-quoted string" means PostgreSQL encountered the start of a dollar-quoted string but never found the closing delimiter. This error is almost never a PostgreSQL parsing bug. Instead, it's caused by SQL clients, JDBC drivers, database tools, or migration systems that don't understand dollar quoting syntax. These tools often try to split SQL statements on semicolons (;) without considering that semicolons inside function bodies (within dollar quotes) should not terminate the statement. When the client sends fragments of the statement separately, the closing delimiter never arrives, resulting in the "unterminated" error. Common scenarios: using JDBC drivers that pre-split queries, executing SQL from FME Workbench or HeidiSQL without proper tool configuration, running migrations through Liquibase that don't understand dollar-quoted functions, or using IDE SQL editors that don't fully support PostgreSQL syntax.

Typical symptoms

  • Error message "Unterminated dollar-quoted string at or near $$" appears when executing CREATE FUNCTION or DO statements
  • Code works perfectly in pgAdmin or psql, but fails in IDE or client tool
  • Error occurs only when using $$...$$, $BODY$...$BODY$, or similar dollar quoting
  • Temporary tables or simple SQL queries work fine in the same tool
  • JDBC or driver-based tools fail with this error while native tools succeed
  • Migration tools fail during function/procedure creation with dollar quote syntax
  • Error persists even when SQL syntax is visually correct

Common causes

  1. SQL client or JDBC driver does not understand dollar quoting and splits statements at every semicolon (including those inside function bodies)
  2. Tool sends only partial statements to PostgreSQL server, leaving dollar-quoted string unclosed
  3. FME, HeidiSQL, DBVisualizer, or other GUI tools have limited PostgreSQL syntax support
  4. IDE SQL execution features that attempt to parse and split statements on semicolons
  5. Liquibase or other migration tools not configured with proper delimiters for dollar-quoted functions
  6. DBeaver or DataGrip versions that have bugs with dollar quoting support
  7. Network issues causing partial statement transmission (rare)
  8. Incorrect line endings (CRLF vs LF) confusing the SQL parser or client

How to fix "Unterminated dollar-quoted string"

1Test with psql or pgAdmin first

Always verify the SQL is correct by running it directly with psql (PostgreSQL command-line) or pgAdmin Query Tool. These tools correctly understand dollar quoting.

bash
# Using psql
psql -U username -d database_name -c "CREATE OR REPLACE FUNCTION my_func() RETURNS void AS $$
BEGIN
  SELECT 1;
END;
$$ LANGUAGE plpgsql;"

Or paste the entire CREATE FUNCTION statement into pgAdmin Query Tool and execute. If it works in psql/pgAdmin but fails in your IDE or client, the SQL is correct and the issue is the tool.

2Use the tool's "raw query" or "batch file" mode

Many tools have options to execute raw SQL without trying to parse it. Use this mode if available:

- pgAdmin: Use "Query Tool" instead of the SQL editor
- DBeaver: Right-click → Execute Script (or SQL → Execute)
- HeidiSQL: Copy entire statement into Query tab and execute as single query
- JDBC: Pass the entire SQL as a single string to executeQuery() without pre-splitting
- IDEs: Look for "Execute as Script" or "Execute as Raw SQL" options

Avoid tools that auto-split statements on semicolons when executing PL/pgSQL or DO blocks.

3Save SQL in a .sql file and execute with psql

If your tool doesn't support dollar quoting properly, bypass it entirely by using psql:

bash
# File: create_function.sql
CREATE OR REPLACE FUNCTION process_data(id INT) RETURNS void AS $$
BEGIN
  UPDATE users SET processed = true WHERE user_id = id;
  INSERT INTO logs (message) VALUES ('Processed user: ' || id);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

# Execute with psql
psql -U username -d database_name -f create_function.sql

psql correctly handles dollar quoting and will execute the entire file as one or more complete statements. This is the most reliable method when dealing with function definitions.

4Configure migration tools with correct end delimiters

If using Liquibase, Flyway, or similar migration tools, configure them to recognize dollar-quoted string boundaries:

Liquibase example:

xml
<changeSet id="create-func" author="name">
  <sql endDelimiter=";"
       splitStatements="false">
    CREATE OR REPLACE FUNCTION my_func() RETURNS void AS $$
    BEGIN
      SELECT 1;
    END;
    $$ LANGUAGE plpgsql;
  </sql>
</changeSet>

Key: Set splitStatements="false" to prevent the tool from splitting on internal semicolons. This tells Liquibase to treat the entire SQL block as one statement.

Flyway note: Flyway has limited dollar-quote support; consider using flyway.sqlMigrationSeparator or splitting functions into separate statements.

5If required, substitute dollar quotes with escaped single quotes

As a last resort (if tool support is unavailable), replace dollar quoting with single-quoted strings, escaping internal quotes. This is not recommended but may work with legacy tools:

sql
-- Original with dollar quotes (RECOMMENDED)
CREATE OR REPLACE FUNCTION greet(name text) RETURNS text AS $$
BEGIN
  RETURN "Hello, " || name || "!";
END;
$$ LANGUAGE plpgsql;

IMPORTANT: This is a workaround only. Dollar quoting is recommended for readability and correctness. Only use single quotes if absolutely necessary, and revert to dollar quoting once tool support is available.

6Check and fix line endings if using cross-platform tools

SQL tools and clients may misinterpret mixed line endings (CRLF vs LF), potentially breaking dollar-quote detection:

bash
# Check line endings in your SQL file
file create_function.sql
# Output: "create_function.sql: ASCII text, with CRLF line terminators"

# Convert CRLF to LF if needed
dos2unix create_function.sql  # On Linux/Mac

# Or with sed:
sed -i 's/\r$//' create_function.sql

Most PostgreSQL tools expect LF line endings. This is especially important when copying code from Windows to Linux or vice versa.

7Update IDE or client tool to latest version

Dollar quoting support has improved in recent versions of many tools:

- DBeaver: Update to the latest version (6.2.3+ has better PostgreSQL support)
- DataGrip/IntelliJ: Update IDE and PostgreSQL plugin
- VS Code: Use the "PostgreSQL" extension and keep it updated
- pgAdmin: pgAdmin 4 has full dollar-quote support (pgAdmin 3 and older have issues)
- JDBC Driver: Use PostgreSQL JDBC driver version 42.3.0+

If you're on an older version, upgrading may resolve the issue entirely.

Advanced notes

When creating recursive or complex PL/pgSQL functions, use descriptive dollar-quote tags to improve readability and avoid tag conflicts:

sql
CREATE OR REPLACE FUNCTION recursive_search(id INT) RETURNS TABLE (node_id INT, level INT) AS $body$
WITH RECURSIVE search_tree AS (
  SELECT id, 0 as level FROM nodes WHERE id = $1
  UNION ALL
  SELECT c.id, st.level + 1 FROM nodes c JOIN search_tree st ON c.parent_id = st.node_id
)
SELECT node_id, level FROM search_tree;
$body$ LANGUAGE sql;

The $body$ tag makes it clear this is the function body and avoids conflicts if your code contains $$.

For testing dollar-quote support in your current tool, try a simple DO block first:

sql
DO $$
BEGIN
  RAISE NOTICE 'Dollar quoting works!';
END;
$$ LANGUAGE plpgsql;

If this simple DO block succeeds, your tool supports dollar quoting. If it fails, the tool definitely needs configuration changes or replacement.

When debugging unterminated dollar-quote errors from a driver or library, enable query logging to see exactly what SQL is being sent to PostgreSQL. Often you'll see that the statement is being split incorrectly:

bash
-- What you sent:
CREATE FUNCTION f() AS $$ BEGIN SELECT 1; END; $$ LANGUAGE plpgsql;

-- What the driver sent (incorrectly split):
CREATE FUNCTION f() AS $$ BEGIN SELECT 1;  
-- (statement ends here, leaving $$ ... $$ unterminated)
END; $$ LANGUAGE plpgsql;

Once you see this, fix the driver/tool configuration to not split on semicolons within dollar-quoted strings.

Frequently asked questions

What does "Unterminated dollar-quoted string" mean?
$$) as an alternative to single-quoted strings, particularly useful in function definitions and procedural code. Dollar-quoted strings consist of an opening $$ (or $tag$ for named quotes), arbitrary content, and a closing $$ (or $tag$). The error "Unterminated dollar-quoted string" means PostgreSQL encountered the start of a dollar-quoted string but never found the closing delimiter.
How do I fix "Unterminated dollar-quoted string"?
The PostgreSQL "Unterminated dollar-quoted string" error occurs when a dollar-quoted string ($$...$$) is not properly closed, typically due to SQL client tools not understanding dollar quoting syntax and incorrectly splitting statements at internal semicolons. Dollar quoting is a PostgreSQL feature for writing string constants without escaping nested quotes, but many tools lack proper support for it.
What causes "Unterminated dollar-quoted string"?
SQL client or JDBC driver does not understand dollar quoting and splits statements at every semicolon (including those inside function bodies) Tool sends only partial statements to PostgreSQL server, leaving dollar-quoted string unclosed FME, HeidiSQL, DBVisualizer, or other GUI tools have limited PostgreSQL syntax support
Is the "Unterminated dollar-quoted string" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading