Last checked and updated on 06 Jun 2026
What Is New in PostgreSQL 6.4
PostgreSQL 6.4 delivers a solid set of enhancements focused on performance, SQL standard compliance, and reliability. This release builds a stronger foundation for complex queries and larger databases.
| Category | Key Changes |
|---|---|
| New Features | Subselects, UNION and INTERSECT queries, NOT NULL constraints, DEFAULT values |
| Performance | Faster index creation, improved optimizer for subselects, reduced lock contention |
| SQL Compliance | Full support for SQL92 entry-level conformance, new reserved keywords |
| Bug Fixes | Numerous fixes for backend crashes, memory leaks, and data corruption issues |
How did PostgreSQL 6.4 improve query capabilities?
The biggest leap forward is the introduction of subselects. You can now nest SELECT statements within WHERE clauses, opening up a whole new class of complex queries that were previously impossible or required messy workarounds.
Alongside subselects, 6.4 adds support for UNION, INTERSECT, and EXCEPT operations. This lets you combine result sets from multiple queries directly in SQL, which is huge for reporting and data analysis tasks. The optimizer also got smarter about handling these new constructs efficiently.
What performance optimizations were included?
Index creation received a significant speed boost, especially for large tables. This matters because adding indexes on production systems often meant dealing with unacceptable downtime, and this improvement directly reduces that window.
The backend also saw reductions in lock contention. In practice, this means better performance under load when multiple clients are reading and writing to the database concurrently, preventing bottlenecks on busy systems.
How did 6.4 enhance data integrity?
This release introduced NOT NULL column constraints. You can finally enforce that critical fields like a user_id or email must always have a value, preventing bad data from entering your system at the database level.
Support for DEFAULT values was also added. This allows you to define a default value for a column, which is used automatically when an INSERT statement doesn't specify a value for it, simplifying application logic.
Were there any breaking changes to be aware of?
Yes, to achieve SQL92 compliance, several new reserved keywords were added. If you used words like
INTERSECT, EXCEPT, TRUE, FALSE, or UNION as
identifiers (table or column names), your applications would break after an upgrade.
The recommended path was to rename these identifiers before upgrading. The parser became stricter, so existing applications with non-standard SQL might have needed adjustments to comply with the new parsing rules.
FAQ
Can I use correlated subqueries in PostgreSQL 6.4?
No, the initial implementation of
subselects in 6.4 does not include support for correlated subqueries. A subquery cannot reference columns from
the outer query. This is a limitation of this first release.
Does the UNION operator remove duplicate rows by default?
Yes, the default behavior for
UNION is to behave like UNION DISTINCT, eliminating duplicate rows from the combined
result set. If you want to keep duplicates, you must explicitly use UNION ALL.
I use 'comment' as a column name. Will it break in 6.4?
No, the keyword COMMENT
was already reserved in a previous version (6.3). The new reserved keywords in 6.4 are primarily
INTERSECT, EXCEPT, TRUE, FALSE, and UNION.
Always check the full list before upgrading.
How do I add a default value to an existing column?
You can use the ALTER TABLE
command with the new SET DEFAULT clause. For example:
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';.
Were there any specific fixes for aggregate functions?
Yes, several bugs related to aggregate
functions were squashed. One notable fix resolved a issue where COUNT(*) could return an negative
value on very large tables, which was a serious data correctness problem.