Save a ColdFusion array into a PostgreSQL array type field

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?

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.