I am using ColdFusion 2021 and PostgreSQL.

I have a table with a text[] type column that I need to populate and am having trouble making ColdFusion play nice.

Sample table

I am using ColdFusion 2021 and PostgreSQL.

I have a table with a text[] type column that I need to populate and am having trouble making ColdFusion play nice.

Sample table

The problem seems to be that ColdFusion arrays do not want to convert to a type Postgres likes.

    <cfset testarray = ['one', 'two', 'three'] />
    <cfquery>
        UPDATE  test_table
           set  keywords = CAST(<cfqueryparam value="#testarray#"
                    cfsqltype="cf_sql_array" /> as text[])
         where  pid = 1
    </cfquery>

That gives me the error Cannot cast an instance of coldfusion.runtime.Array to type Types.ARRAY (that error happens with or without the explict CAST)

Not much better if I try to convert it to a java array type.

    <cfset testarray = ['one', 'two', 'three'] />
    <cfquery>
        UPDATE  test_table
           set  keywords = CAST(<cfqueryparam value="#javaCast("string[]", testarray)#" 
                    cfsqltype="cf_sql_array" /> as text[])
         where  pid = 1
    </cfquery>

Produces Object of type class java.lang.String cannot be used as an array

I really don't want to have to loop over the array and update the keywords column with each value because that would trigger a logging event for each update which would not be a desirable outcome if I can avoid it.

So what's the trick to saving a ColdFusion array into a Postgres array?

• postgresql
• coldfusion

• 2 I'm not familiar with PostGres, but I am familiar with CF. I found a PostGres tutorial that shows you may use one of two syntaxes: ARRAY [ value1,value2] or '{value1,value2}' I'm wondering if you can use Cf's arrayToList() within your <cfqueryparam> tag with list="yes" attribute. – mykaf Commented Mar 26 at 16:44
• You may be on to something, but so far it is giving me Object of type class java.lang.String cannot be used as an array or it is complaining index out of bounds because it thinks the list indicates more column names – Stephen F Roberts Commented Mar 26 at 18:33
• 1 If the queryparam is expecting an array ("cf_sql_array"), then passing a string (javaCast("string[]", testarray) would be expected to throw an error. What happens if you don't javacast that? Will it accept an array as-is, without casting or javacasting? set keywords = <cfqueryparam value="#testarray#" cfsqltype="cf_sql_array" />? – mykaf Commented Mar 26 at 18:41
• 1 Would you update your question to show what you tried with my suggestion(s) using arrayToList()? – mykaf Commented Mar 26 at 19:05
• 1 Got it (at least so far). Your idea of treating the array as a list worked once I worked out how to cast that list back into a Postgres array! – Stephen F Roberts Commented Mar 27 at 12:55

1 Answer 1

Found it!!!

User mykaf sent me down the road of looking into treating the array as a list and I figured out how to cast a list of values into an array builder and that worked (at least it is working so far for all my test values)!

    <cfset testarray = ['one', 'two', 'three'] />
    <cfquery>
        UPDATE  test_table
           set  keywords = ARRAY[<cfqueryparam value="#arrayToList(testarray)#" list="yes" />]
         where  pid = 1
    </cfquery>

Important caveat I found so far:

If one of the array elements has a comma in it, it will be treated as two entries, though that might be mitigated if you use a different list delimiter (not a problem for my system because it blocks commas from the values before the query). The <cfqueryparam seems to do a good job eating other special characters to prevent weird results.