The PostgreSQL 'unterminated quoted string' error occurs when a SQL query contains a string literal that is missing its closing single quote. This is a parse-time syntax error that prevents the query from executing. Common causes include missing closing quotes, unescaped quotes within strings, or improper string literal formatting.
The "unterminated quoted string" error appears when PostgreSQL's SQL parser encounters a string literal that is missing its closing single quote ('). In PostgreSQL, all string literals must be enclosed in single quotes ('), and the closing quote must appear before the statement ends or before the next major SQL token. When the parser reaches the end of a line or statement delimiter (semicolon, parenthesis, or keyword) without finding a closing quote, it reports this error. This is different from runtime errors—it's a parse-time syntax error that prevents compilation of the query entirely. The error message typically includes the position where PostgreSQL expected to find the closing quote.
PostgreSQL's error message includes the position where it found the problem. Use this to locate the problematic string literal.
-- INCORRECT: Missing closing quote
SELECT * FROM users WHERE email = '[email protected];
-- Error: ERROR: unterminated quoted string at or near "'john"The error points to the opening quote position. Look at your query starting from that point and trace forward to find the missing closing quote.
In PostgreSQL, string literals must use single quotes ('), not double quotes. Double quotes are reserved for identifiers (table names, column names).
-- INCORRECT: Double quotes for string literal (this is wrong)
SELECT * FROM users WHERE name = "John";
-- CORRECT: Single quotes for string literals
SELECT * FROM users WHERE name = 'John';
-- CORRECT: Double quotes for identifiers (column/table names)
SELECT "FirstName", "LastName" FROM users WHERE "Status" = 'active';Review your query and ensure:
- String literal values use single quotes: 'value'
- Column and table names use double quotes if they contain mixed case: "ColumnName"
- No mixing of quote types for the same purpose
If your string value contains a single quote (apostrophe), you must escape it by doubling the quote:
-- INCORRECT: Unescaped single quote causes unterminated string
INSERT INTO products (name) VALUES ('John's Book');
-- Error: ERROR: unterminated quoted string
-- CORRECT: Double the single quote to escape it
INSERT INTO products (name) VALUES ('John''s Book');
-- Also correct: Use escape syntax (if E prefix is used)
INSERT INTO products (name) VALUES (E'John\'s Book');When building queries programmatically, use parameterized queries instead of string concatenation to avoid this issue entirely:
// Node.js with pg library - safe from quote issues
const result = await client.query(
'INSERT INTO products (name) VALUES ($1)',
['John\'s Book'] // No need to double quotes; parameterization handles escaping
);When copying SQL from documents, websites, or word processors, smart quotes (curly quotes) or Unicode variants may replace standard ASCII quotes. These are not recognized as valid SQL quotes.
-- INCORRECT: Smart/curly quotes from copy-paste (not ASCII single quotes)
SELECT * FROM users WHERE name = 'John';
-- The quotes here are Unicode U+2018 and U+2019, not U+0027
-- CORRECT: Use ASCII single quotes (U+0027)
SELECT * FROM users WHERE name = 'John';To fix this:
1. Paste the SQL into a plain-text editor first
2. Delete the problematic quotes and retype them manually using your keyboard
3. Use a code editor with SQL syntax highlighting to catch these issues
4. Configure your code editor to always use straight quotes, not smart quotes
The best practice is to never concatenate user input directly into SQL strings. Use parameterized queries (prepared statements) instead, which handle quoting automatically.
// BAD: String concatenation (vulnerable to quote issues and SQL injection)
const email = "[email protected]";
const query = "SELECT * FROM users WHERE email = '" + email + "'";
// GOOD: Parameterized query (safe)
const { Client } = require('pg');
const client = new Client();
const email = "[email protected]";
const result = await client.query(
'SELECT * FROM users WHERE email = $1',
[email] // Parameters are handled safely
);Most database libraries support parameterized queries:
- PostgreSQL pg: query('...', [param])
- Prisma: ORM handles quoting automatically
- TypeORM: createQueryBuilder(...).setParameter(...)
- SQLAlchemy: text(...).bindparams(...)
String literals can span multiple lines in PostgreSQL, but the quotes must still be properly closed:
-- INCORRECT: String spans multiple lines without proper closing on first line
SELECT * FROM users WHERE description = 'This is a long description
that continues on the next line;
-- CORRECT: String properly closed on same logical line (or next quote found)
SELECT * FROM users WHERE description = 'This is a long description
that continues on the next line';
-- Also correct: Use || for string concatenation across lines
SELECT * FROM users WHERE description = 'This is a long description '
|| 'that continues on the next line';When building multi-line SQL strings in application code, ensure quotes are properly balanced:
// CORRECT: Multi-line SQL with proper quotes
const query = `
SELECT * FROM users
WHERE name = $1
AND status = $2
`;Modern development tools can catch unterminated string errors before you run the query:
- VS Code: Install "SQLTools" or "SQL Formatter" extension
- JetBrains IDEs: Built-in SQL inspection
- pgAdmin: Web-based editor with syntax checking
- DBeaver: Desktop SQL client with real-time validation
Before executing any SQL:
1. Copy the query into a dedicated SQL editor with syntax highlighting
2. Look for color changes that indicate unclosed strings
3. Use "Format SQL" feature to clean up formatting issues
4. Review the query visually for matching quotes
Example of syntax highlighting detecting the error:
SELECT * FROM users WHERE name = 'John; -- String highlighted in red (unclosed)SQL Dialects and Quote Handling: PostgreSQL strictly follows SQL standards for quoting. Some other databases (MySQL, SQL Server) allow double quotes for string literals by default, which can cause confusion when migrating code.
If you're migrating from MySQL to PostgreSQL:
- MySQL allows: SELECT * FROM users WHERE name = "John";
- PostgreSQL requires: SELECT * FROM users WHERE name = 'John';
Set sql_mode = 'ANSI_QUOTES' in MySQL to enforce stricter compliance.
Dollar-Quoted Strings: PostgreSQL supports an alternative quoting syntax using dollar signs for complex strings:
-- Standard quoting requires escaping internal quotes
INSERT INTO docs (content) VALUES ('It\'s a long\'s story');
-- Dollar quotes avoid escaping issues
INSERT INTO docs (content) VALUES $$It's a long's story$$;
-- Named tags allow nesting
INSERT INTO docs (content) VALUES $tag$Content with $$ and quotes'$tag$;Dollar-quoted strings are especially useful in function definitions and for strings containing many quotes.
Debugging Tools: When working with complex queries, use these debugging techniques:
1. Break the query into parts and test each section:
-- Test just the WHERE clause
SELECT * FROM users WHERE email = '[email protected]';
-- Then add complexity
SELECT id, name FROM users WHERE email = '[email protected]' AND status = 'active';2. Use EXPLAIN to check if the query is valid before expensive execution:
EXPLAIN SELECT * FROM users WHERE name = 'John';3. Check application logs for the exact SQL being executed—copy this verbatim into a SQL client to reproduce the error locally.
Character Set Issues: Ensure your database and connections use UTF-8 encoding. Some character set issues can cause quote characters to be misinterpreted:
-- Check database encoding
SELECT datcollate FROM pg_database WHERE datname = current_database();
-- Check connection encoding
SHOW client_encoding;
-- Set correct encoding (usually UTF8)
SET client_encoding = 'UTF8';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