postgresql UUID equivalent in coldFusion

Question

I have a table session which is having sess_uuid column of type UUID in postgres , my problem is that I have to update session table from coldFusion cfQuery using query :

UPDATE sessions
    SET     sess_frn_usr_id = 1 
    WHERE   sess_uuid = <cfqueryparam value="#arguments.SessionToSave.get('sessUuid')#" cfsqltype="cf_sql_other">

Problem in this query is that sess_uuid column is of type UUID in database and here in ColdFusion the equivalent type is cf_sql_other which is not working for this. The error is:

ERROR: operator does not exist: uuid = character varying

Which cf_sql type in ColdFusion is to be used here to map UUID column in postgres?

I don't have Postgres installed here to verify, but googling says that people have fixed this issue by updating the JDBC drivers for Postgres. — Joe C
Most of the posts are old, but were talking about upgrading to 8.3. — Joe C
@phantom tested with that too, not at all solving this sucking issue , and it is very urgent for me to solve it out, still thanks to you for helping me in this. — Satish Sharma
This is not a great solution in the long term, but you could cast the uuid column to varchar. Then it would work with a string: WHERE CAST(sess_uuid AS varchar) = <cfqueryparam value="(some value)" cfsqltype="cf_sql_varchar"> — Leigh

Leigh Leigh · Accepted Answer · 2013-01-03T23:33:23

I tested your query with both CF9.0 and 10, against PostgreSQL 9.1, and type cf_sql_other worked just fine. So either that is not the actual query causing the error or perhaps something else is going on beyond what we can see. Does your DSN have any special jdbc connection settings?

    ERROR: operator does not exist: uuid = character varying
Which cf_sql type in ColdFusion is to be used here to map UUID column in postgres?

Based on my limited tests and a few quick searches, cf_sql_other is the correct type to use with the PostgreSQL driver bundled in CF9+. There is no straight equivalent for PostgreSQL's uuid type in jdbc 3, so type OTHER is used to indicate it is database-specific, and the driver internals take it from there.

I was only able to reproduce your error by using one of the string types, like cf_sql_varchar or omitting the cfsqltype so it defaults to cf_sql_char. Given the error message, direct comparisons between uuid and strings are not supported. You must explicitly cast the uuid to a string first (or vice versa). All of the examples below worked for me with CF9+ and PostgreSQL 9.1.

  <!--- uuid = "d0c2b125-9222-d479-fb4a-be912b8c7c3b" --->
  <!--- syntax 1 ---->
  WHERE   CAST(sess_uuid AS VARCHAR)  = <cfqueryparam value="#uuid#" cfsqltype="cf_sql_varchar">   

  <!--- syntax 2 ---->
  WHERE   sess_uuid::text  = <cfqueryparam value="#uuid#" cfsqltype="cf_sql_varchar">   

  <!--- syntax 3 --->
  WHERE   sess_uuid  = <cfqueryparam value="#uuid#" cfsqltype="cf_sql_varchar">::uuid

Though I would not recommend it, it is worth noting that omitting cfqueryparam also works. Since a direct comparison with a varchar value fails (even in postgresql) I guess there is some extra conversion magic happening in this one case.

  <!--- fails --->
  WHERE   sess_uuid = CAST('#uuid#' AS VARCHAR)

  <!--- works --->
  WHERE   sess_uuid = '#uuid#'

postgresql UUID equivalent in coldFusion

2 Answers