如何将ColdFusion数组存入PostgreSQL的数组类型字段？

下面提供两种高效的解决方法，无需循环更新：

方法一：构造PostgreSQL原生数组格式字符串 #

直接将ColdFusion数组转换为PostgreSQL识别的数组字符串格式，配合<cfqueryparam>传入：

1. 先转义数组元素中的单引号，避免SQL语法错误和注入风险
2. 拼接成'{元素1,元素2,...}'格式的字符串
3. 用CF_SQL_VARCHAR类型传入查询

示例代码：

<cfset keywordArray = ["java", "coldfusion's tips", "postgresql"]>

<!--- 转义元素中的单引号 --->
<cfset escapedKeywords = arrayMap(keywordArray, function(item) {
  return replace(item, "'", "''", "all");
})>

<!--- 拼接为PostgreSQL数组格式字符串 --->
<cfset pgArrayStr = "{'#arrayToList(escapedKeywords, "','")#'}">

<cfquery datasource="yourPostgreSQLDSN">
  INSERT INTO your_table (pid, keywords)
  VALUES (
    <cfqueryparam value="456" cfsqltype="CF_SQL_INTEGER">,
    <cfqueryparam value="#pgArrayStr#" cfsqltype="CF_SQL_VARCHAR">
  )
</cfquery>

方法二：使用JDBC原生Array对象（更规范） #

通过ColdFusion获取JDBC连接，创建PostgreSQL兼容的java.sql.Array对象，直接传入查询：

<cfset keywordArray = ["java", "coldfusion", "postgresql"]>

<!--- 获取JDBC连接 --->
<cfset dsService = createObject("java", "coldfusion.server.ServiceFactory").getDataSourceService()>
<cfset conn = dsService.getDataSource("yourPostgreSQLDSN").getConnection()>

<!--- 创建PostgreSQL text类型的数组对象 --->
<cfset pgArray = conn.createArrayOf("text", keywordArray.toArray())>

<cfquery datasource="yourPostgreSQLDSN">
  INSERT INTO your_table (pid, keywords)
  VALUES (
    <cfqueryparam value="456" cfsqltype="CF_SQL_INTEGER">,
    <cfqueryparam value="#pgArray#" cfsqltype="CF_SQL_OTHER">
  )
</cfquery>

<!--- 关闭连接 --->
<cfset conn.close()>

之前尝试失败的原因 #

• 直接传ColdFusion数组：ColdFusion的coldfusion.runtime.Array和JDBC要求的java.sql.Array不是同一类型，无法直接强制转换，导致类型不匹配报错。
• 转Java数组报错：大概率是转换过程中元素类型不对，或者误将字符串当作数组传入，需要确保转换后的是String类型的Java数组，再通过JDBC创建对应的数组对象。

内容的提问来源于stack exchange，提问作者Stephen F Roberts