Yesterday I started a post talking about PostgreSQL 8.3 RC2 and this morning I come in to find the official 8.3 release in my inbox! There are quite a few interesting new features, a few of which are specifically of interest to ColdFusion developers:
- UUID datatype
- ANSI-standard SQL/XML support
- ENUM datatype
There is a page about UUID support in the documentation. It supports the standard 36-character GUID/UUID rather than ColdFusion’s 35-character (missing one of the hyphens). There a discussion of the differences from Rob Gonda with some MSSQL-specific solutions to the matter. Since Postgres doesn’t currently generate the ID, I’m not sure that storing it in this format is any better than CHAR(35). It’s probably only a matter of writing a small wrapper function to add/remove the hyphen as needed. It’s worth checking though since many ColdFusion developers use UUIDs as primary keys (myself included).
They’ve included an interesting feature matrix that shows the progress of Postgres over the last few major versions. Great to see how far the team has come!
If you’re on a YUM-compatible platform like RHEL or CentOS, you can use the Postgres YUM repository for easy installs and automatic updates. Highly recommended.
I’ll heap some additional praise onto the CLAP stack of ColdFusion, Linux, Apache and PostgreSQL. I’ve been using this combination for several years now with great success. Here’s a list I stole from the postgres docs on the high-level technical features of 8.3:
- Fully ACID compliant.
- ANSI SQL compliant.
- Referential Integrity.
- Replication (non-commercial and commercial solutions) allowing the duplication of the master database to multiple slave machines.
- Native interfaces for ODBC, JDBC, .Net, C, C++, PHP, Perl, TCL, ECPG, Python, and Ruby.
- Outer Joins.
- An open API.
- Stored Procedures.
- Native SSL support.
- Procedural languages.
- Hot stand-by (commercial solutions).
- Better than row-level locking.
- Functional and Partial indexes.
- Native Kerberos authentication.
- Support for UNION, UNION ALL and EXCEPT queries.
- Loadable extensions offering SHA1, MD5, XML, and other functionality.
- Tools for generating portable SQL to share with other SQL-compliant systems.
- Extensible data type system providing for custom, user-defined datatypes and rapid development of new datatypes.
- Cross-database compatibility functions for easing the transition from other, less SQL-compliant RDBMS.
Postgres has 95% of the features of big commercial systems like Oracle but doesn’t suffer from data consistency issues like other open source databases. It also runs great on Windows which makes development a breeze. With backing from Sun, it will only continue to become more popular.