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.
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.
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.
# 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.
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.
If your tool doesn't support dollar quoting properly, bypass it entirely by using psql:
# 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.sqlpsql 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.
If using Liquibase, Flyway, or similar migration tools, configure them to recognize dollar-quoted string boundaries:
Liquibase example:
<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.
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:
-- 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.
SQL tools and clients may misinterpret mixed line endings (CRLF vs LF), potentially breaking dollar-quote detection:
# 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.sqlMost PostgreSQL tools expect LF line endings. This is especially important when copying code from Windows to Linux or vice versa.
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.
When creating recursive or complex PL/pgSQL functions, use descriptive dollar-quote tags to improve readability and avoid tag conflicts:
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:
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:
-- 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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL