在Go应用中使用Postgres数组查询Redshift表的问题

Let's break down why you're hitting this problem and how to fix it.

The Root Cause #

Redshift is built on PostgreSQL, but it has significant differences in how it handles array parameters compared to standard PostgreSQL. The pq.Array() helper from lib/pq generates a format that works seamlessly for PostgreSQL, but Redshift doesn't recognize this format natively when used as a parameter in the ANY() clause.

When you run select * from table where colID = ANY(array[1]) directly in SQL Workbench, you're using Redshift's native array literal syntax—which it understands perfectly. But when you pass pq.Array([]int{1}) as a parameter, lib/pq sends it in a PostgreSQL-specific format that Redshift can't parse correctly for this context.

Fixes to Try #

1. Use Redshift-Friendly IN Clause with Dynamic Placeholders (SQL Injection Safe) #

Instead of relying on ANY(), convert your array into a list of parameterized placeholders. This approach is fully compatible with Redshift and avoids SQL injection risks:

import (
    "fmt"
    "strings"
    "database/sql"
    _ "github.com/lib/pq"
)

func main() {
    ids := []int{1, 2, 3}
    // Generate placeholders like $1, $2, $3
    placeholders := make([]string, len(ids))
    args := make([]interface{}, len(ids))
    
    for i, id := range ids {
        placeholders[i] = fmt.Sprintf("$%d", i+1)
        args[i] = id
    }
    
    query := fmt.Sprintf(`select * from table where colID IN (%s)`, strings.Join(placeholders, ", "))
    rows, err := db.Query(query, args...)
    
    if err != nil {
        // Handle error (e.g., type assert to *pq.Error for details)
        pqErr := err.(*pq.Error)
        fmt.Printf("Redshift error: %v\n", pqErr)
    }
}

2. Unnest the Array (Redshift-Compatible Parameterized Approach) #

Redshift supports unnest() for array handling. You can rewrite your query to join with an unnested array parameter, explicitly casting it to Redshift's expected integer array type:

rows, err := db.Query(`select t.* from table t
join unnest($1::int[]) as arr(id) on t.colID = arr.id`, pq.Array([]int{1}))

if err != nil {
    pqErr := err.(*pq.Error)
    fmt.Printf("Redshift error: %v\n", pqErr)
}

3. Generate Redshift Array Literals (Only for Trusted Input!) #

If you're working with fully trusted input (never user-provided data!), you can manually create a Redshift-compatible array literal string:

ids := []int{1}
// Generate "array[1]" from the slice
arrayLiteral := fmt.Sprintf("array[%s]", strings.Trim(strings.Join(strings.Fields(fmt.Sprint(ids)), ","), "[]"))

rows, err := db.Query(`select * from table where colID = ANY($1)`, arrayLiteral)
if err != nil {
    // Handle error
}

Critical Warning: This method is vulnerable to SQL injection if used with untrusted input. Always use parameterized queries for user-provided data.

Why These Work #

Redshift's parameter handling for arrays is stricter than PostgreSQL's. The first two approaches use patterns Redshift explicitly supports: discrete parameters for the IN() clause, or joining with an unnested array via a parameterized cast. These bypass the compatibility gap between lib/pq's array formatting and Redshift's expectations.

内容的提问来源于stack exchange，提问作者Pranavi Chandramohan