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:
Technical Features
- 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.
- Rules.
- Views.
- Triggers.
- Unicode.
- Sequences.
- Inheritance.
- Outer Joins.
- Sub-selects.
- 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.
All Categories from Feedburner
Nathan D said:
on February 4, 2008 at 1:53 pm
I have not looked at all at this version of PostgreSQL, but I seem to recall back in the day there were reasons to use the 36-char ID with MS SQL Server due to the way the underlying engine dealt with the indexing — something about storing it in some more efficient form than a char(35) (and my lack of specific knowledge on this is a clear sign I ought not be posting this suggestion).
And since I was literally shocked to find out how long ago I wrote this function, I will give the obligatory link to the (silly simple) createGUID function on CFLib: http://www.cflib.org/udf.cfm?ID=54
brian said:
on February 5, 2008 at 9:37 am
MSSQL does have a “uniqueidentifier” type which is a GUID storage bin that does something numeric or bit behind the scenes. I’m guessing that Postgres’ implementation is similar.
It’s interesting there still isn’t a createGUID() function in ColdFusion since you can check isValid(“guid”, value). Check but not create? Kind of silly. The livedocs even point to your function on cflib.org!
Jochem van Dieten said:
on February 6, 2008 at 3:29 am
The PostgreSQL impelmentation is to remove all hyphens, convert the resulting 32 hex characters to a 16-byte string and use that string internally. Only when outputted to a front-end it is converted back to a 36-byte string.