How to fix syntax error at or near 'UNION' in PostgreSQL

The most common cause of this error is placing ORDER BY or LIMIT on an individual SELECT before combining with UNION.

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

-- CORRECT: ORDER BY and LIMIT after UNION
SELECT id, name FROM users
UNION
SELECT id, name FROM archived_users
ORDER BY id
LIMIT 10;

If you need to ORDER BY or LIMIT individual subqueries before combining them, wrap each in parentheses:

(SELECT id, name FROM users ORDER BY id LIMIT 5)
UNION
(SELECT id, name FROM archived_users ORDER BY id LIMIT 5)
ORDER BY id;

When using parentheses, ensure all SELECT statements in the UNION are consistently parenthesized, especially with LIMIT, ORDER BY, or CTEs.

```sql
-- INCORRECT: Only first is wrapped
(SELECT id, name FROM users LIMIT 10)
UNION
SELECT id, name FROM archived_users;

-- CORRECT: Both wrapped consistently
(SELECT id, name FROM users LIMIT 10)
UNION
(SELECT id, name FROM archived_users LIMIT 10);

-- Also correct: Neither wrapped (when no LIMIT/ORDER BY per subquery)
SELECT id, name FROM users
UNION
SELECT id, name FROM archived_users;

All SELECT statements in a UNION must return the same number of columns in the same order.

```sql
-- INCORRECT: Different number of columns
SELECT id, name, email FROM users
UNION
SELECT id, name FROM archived_users; -- Only 2 columns

-- CORRECT: Same columns in same order
SELECT id, name, email FROM users
UNION
SELECT id, name, email FROM archived_users;

-- Or add NULL placeholders for missing columns:
SELECT id, name, email FROM users
UNION
SELECT id, name, NULL as email FROM archived_users;

PostgreSQL requires compatible data types across UNION columns. While type coercion may occur, mismatches can cause errors.

```sql
-- INCORRECT: Incompatible types (int vs text)
SELECT id::int, name FROM users
UNION
SELECT code::text, description FROM error_codes;

-- CORRECT: Explicit casting to ensure compatible types
SELECT id::text as code, name as description FROM users
UNION
SELECT code::text, description::text FROM error_codes;

-- Check result types with EXPLAIN:
EXPLAIN SELECT id, name FROM users UNION SELECT id, name FROM archived_users;

Common table expressions (WITH clauses) combined with UNION can cause syntax errors if not properly structured.

```sql
-- INCORRECT: WITH might not apply to second SELECT
WITH active_users AS (
SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users
UNION
SELECT id, name FROM archived_users; -- archived_users not in CTE scope

-- CORRECT: Include all referenced tables
WITH active_users AS (
SELECT id, name FROM users WHERE status = 'active'
),
archived AS (
SELECT id, name FROM archived_users
)
SELECT * FROM active_users
UNION
SELECT * FROM archived;

Query builders (Knex, SQLAlchemy, ORMs) may generate malformed UNION queries. Always validate the generated SQL.

// Example: Knex.js - ensure UNION is called correctly
const query = knex
  .select('id', 'name')
  .from('users')
  .union([
    knex.select('id', 'name').from('archived_users')
  ])
  .orderBy('id');

console.log(query.toString());  // Print SQL to verify it's correct

If the generated SQL looks wrong, consult the query builder's UNION documentation or switch to raw SQL if the builder is not flexible enough.

UNION removes duplicate rows (expensive operation), while UNION ALL keeps all rows (fast). If you don't need deduplication, use UNION ALL.

-- UNION: removes duplicates (sorts results internally)
SELECT id, name FROM users
UNION
SELECT id, name FROM archived_users;  -- Slower

-- UNION ALL: keeps all rows (no sorting)
SELECT id, name FROM users
UNION ALL
SELECT id, name FROM archived_users;  -- Faster

Also check PostgreSQL documentation on query optimization: use EXPLAIN ANALYZE to see if UNION is the bottleneck.