How to fix ERROR: unterminated quoted string in PostgreSQL

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)