In part 1 I showed how to adapt ColdFusion to work with PostgreSQLs native UUID datatype. In this part, I will show how to adapt PostgreSQL to accept ColdFusions native UUID datatype.

UUIDs: equal, but not the same

As ColdFusion developers are very aware thestring representation of a UUID in ColdFusionis a 35 character string. However, the rest of the world has decided on a 36 character string representation. Both in standards (RFC 4122, ITU-T X.667 and The Open Group) as in implementations (MS SQL Servers newID(), Javas java.util.UUID etc.). PostgreSQL is no exception and uses the 36 character string representation. As a result, the output of the ColdFusion CreateUUID() function will not be accepted into a PostgreSQL UUID field and the output of a PostgreSQL UUID field will not be recognized by ColdFusion as a UUID. I actually believe this is a bug in ColdFusions type detection and conversion system. Since CFML is a typeless language, it should just convert both the 35 and the 36 character string representation. ColdFusion will on the fly convert both a m/d/yy string and a yyyy-mm-dd string to a date object, so why not convert both UUID string representations to their native integer representation?

Patching PostgreSQL

Unfortunately ColdFusion is a closed source product and I can not adapt it to work the way I want it to work. However, PostgreSQL is Open Source and I can adapt it. A little digging in the source code reveals that the input and output formats for the UUID datatype are defined in uuid.c and it is trivial to adapt them to use the ColdFusion format. Just apply the patch for PostgreSQL 8.3 to accept a 35-character string representation as UUID and recompile PostgreSQL. Once you have done that, the following code will work:


INSERT INTO test (testID)
VALUES (<cfqueryparam cfsqltype="cf_sql_other" value="#CreateUUID()#">)


  • c is not a language I am comfortable in so the code is probably suboptimal;
  • I do not intend to submit this as a patch to PostgreSQL since they follow all the standards and ColdFusion does not;
  • a permanent solution to the problem of ColdFusion UUIDs clashing with the rest of the world should come from Adobe.

So to go from ColdFusion to PostgreSQL we now can use the 36-character representation as an inline variable, with the updated JDBC driver as a bind variable, and with this patch also using a 35-character string. What is left to do is patching the JDBC driver to convert the 16-byte representation it receives from PostgreSQL to a 35-character string ColdFusion understand. Looks like that requires writing a new type. Unfortunately I will be busy for the rest of the month with a new three week Flex/LiveCycle class we will be teaching so I won’t be able to start on that for a while.


  1. Brian says:


    Forgive the would-be-newbie PG question - I have not worked with new datatypes previously. Is it possible to get this functionality without recompiling postgres? I’ve seen this approach before:

    But don’t know how it changes now that the uuid type is built-in? I manage my postgres with yum on CentOS so I would like to avoid recompiling if at all possible.



  2. Jochem says:

    I don’t think it is currently possible without patching either PostgreSQL or the JDBC driver.

    It is on my list of things to figure out once the PostgreSQL 8.4 beta is out. The patch to make PostgreSQL understand ColdFusion UUIDs was committed last month so at least one side of the equation has gotten easier. The other side is something that Adobe has to fix, hopefully for the ColdFusion 9 timeframe. But I am more and more inclined to just abandon all ColdFusion UUID crap and switch everything to the standard format (which in ColdFusion is known as GUID).

  3. Brian says:

    Interesting idea to just skip to GUIDs. I guess that has better universal support minus the createUUID() function. Converting an existing database to GUIDs might be a semi-PITA if using them as primary/foreign keys. Food for thought, thanks Jochem!

    ps - not having a “subscribe to comments” option on the blog is annoying… no way to know if you or anyone else replies!