How to fix syntax error at or near "TOKEN" in PostgreSQL

PostgreSQL error messages include the specific token and character position where parsing failed. Review the exact token shown in your error message.

ERROR: syntax error at or near "UNION" (character 42)

The error occurred at character 42, near the word UNION. The actual syntax problem usually appears just before the reported token. Open the query in a text editor and count characters (or use your database client's position indicator) to find the exact location.

One of the most common syntax errors is a missing comma in column lists or a trailing comma before FROM/WHERE.

-- INCORRECT: Missing comma between columns
SELECT id name, email FROM users;

-- CORRECT
SELECT id, name, email FROM users;

-- INCORRECT: Trailing comma before FROM
SELECT id, name, FROM users;

-- CORRECT
SELECT id, name FROM users;

Check INSERT statements too:

```sql
-- INCORRECT: Missing comma
INSERT INTO users (id, name email) VALUES (1, 'Alice', '[email protected]');

-- CORRECT
INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]');

Mismatched or extra parentheses are common syntax errors. Count opening and closing parentheses to ensure they match.

```sql
-- INCORRECT: Extra closing parenthesis
SELECT id, name FROM (SELECT * FROM users) AS u);

-- CORRECT
SELECT id, name FROM (SELECT * FROM users) AS u;

-- INCORRECT: Missing closing parenthesis in subquery
SELECT id FROM (SELECT id FROM users WHERE status = 'active';

-- CORRECT
SELECT id FROM (SELECT id FROM users WHERE status = 'active');

-- INCORRECT: Missing parentheses in function call
SELECT COUNT FROM users;

-- CORRECT
SELECT COUNT(*) FROM users;

PostgreSQL distinguishes between single quotes (for string values) and double quotes (for identifiers). Using wrong quotes causes syntax errors.

```sql
-- INCORRECT: Single quotes for identifier
SELECT 'Orders' FROM 'orders' WHERE status = 'active';

-- CORRECT: Double quotes for identifiers, single quotes for values
SELECT Orders FROM "Orders" WHERE status = 'active';
SELECT "Orders" FROM "Orders" WHERE status = 'active';

-- INCORRECT: No quotes around reserved keyword identifier
SELECT grant FROM users; -- grant is reserved, causes syntax error

-- CORRECT: Double quotes around reserved keyword
SELECT "grant" FROM users;
SELECT grant AS permission FROM users; -- alias form also works

SQL clauses must appear in the correct order. The canonical order is SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

```sql
-- INCORRECT: WHERE after GROUP BY
SELECT department, COUNT(*) FROM employees
GROUP BY department
WHERE salary > 50000
ORDER BY COUNT(*) DESC;

-- CORRECT: WHERE before GROUP BY
SELECT department, COUNT(*) FROM employees
WHERE salary > 50000
GROUP BY department
ORDER BY COUNT(*) DESC;

-- INCORRECT: ORDER BY before UNION
SELECT id FROM users ORDER BY id
UNION
SELECT id FROM archived_users;

-- CORRECT: ORDER BY after UNION
SELECT id FROM users
UNION
SELECT id FROM archived_users
ORDER BY id;

PostgreSQL uses specific operators and operators must match expected syntax. Double-equals (==) is not valid; use single equals (=) for assignment/comparison.

```sql
-- INCORRECT: Double equals
SELECT * FROM users WHERE id == 1;

-- CORRECT: Single equals
SELECT * FROM users WHERE id = 1;

-- INCORRECT: Invalid operator
SELECT * FROM users WHERE name >>> 'John';

-- CORRECT: Valid operator
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name LIKE 'J%';

-- Note proper IN syntax
-- INCORRECT
SELECT * FROM users WHERE status IN ('active' 'pending');

-- CORRECT
SELECT * FROM users WHERE status IN ('active', 'pending');

Strings containing single quotes must be properly escaped using doubled single quotes or the E'' syntax.

```sql
-- INCORRECT: Unescaped single quote breaks the string
SELECT * FROM users WHERE name = 'O'Brien';

-- CORRECT: Double the single quote
SELECT * FROM users WHERE name = 'O''Brien';

-- Also correct: Use E'' syntax with backslash
SELECT * FROM users WHERE name = E'O\'Brien';

-- For backslashes, use doubled backslashes
SELECT * FROM files WHERE path = 'C:\\Users\\Data';
SELECT * FROM files WHERE path = E'C:\\\\Users\\\\Data';

If the error originates from configuration files (postgresql.conf or pg_hba.conf), verify the syntax of those files.

# INCORRECT: Invalid token in postgresql.conf
shared_buffers = 256MB MB  # Extra "MB" token

# CORRECT
shared_buffers = 256MB

# pg_hba.conf: Incorrect line format
local   database  user      trust extra_column

# CORRECT pg_hba.conf format
local   database  user      trust
host    all       all       127.0.0.1/32  md5

Validate configuration by restarting PostgreSQL or using pg_ctl check -D /path/to/data.

Query builders (Knex, SQLAlchemy, TypeORM, Prisma) may generate incorrect SQL. Always print or log the generated query to verify it's valid.

// JavaScript/Knex.js
const query = knex
  .select('id', 'name')
  .from('users')
  .where('status', '=', 'active');

console.log(query.toString());  // Print SQL to verify

// Or use Prisma raw query with $queryRaw and .toString()
const result = await prisma.$queryRaw`SELECT * FROM users WHERE id = ${id}`;

If the generated SQL is incorrect, consult the query builder's documentation or switch to raw SQL/parameterized queries if the builder lacks required flexibility.