Go+GORM操作PostgreSQL遇bigint与text类型不匹配错误求助

项目环境与数据库结构 #

使用Go+GORM开发项目，数据库表结构如下：

CREATE TABLE IF NOT EXISTS public.keywords (
    keyword text NOT NULL,
    PRIMARY KEY(keyword)
);

CREATE TABLE IF NOT EXISTS public.people (
    id bigint DEFAULT nextval('people_id_seq'::regclass) NOT NULL,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    deleted_at timestamp with time zone,
    name text,
    PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS public.photos (
    id text NOT NULL,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    deleted_at timestamp with time zone,
    file_name text,
    file_path text,
    metadata_hash text,
    lens_make text,
    lens_model text,
    lens_id text,
    make text,
    model text,
    caption text,
    title text,
    creation_date_time timestamp with time zone,
    width bigint,
    height bigint,
    latitude numeric,
    longitude numeric,
    ip_tc_digest text,
    "year" text,
    PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS public.photos_keywords (
    photo_id text NOT NULL,
    keyword_keyword text NOT NULL,
    PRIMARY KEY(keyword_keyword)
);

ALTER TABLE IF EXISTS public.photos_keywords
    ADD CONSTRAINT fk_photos_keywords_photo
    FOREIGN KEY (photo_id)
    REFERENCES public.photos (id);

ALTER TABLE IF EXISTS public.photos_keywords
    ADD CONSTRAINT fk_photos_keywords_keyword
    FOREIGN KEY (keyword_keyword)
    REFERENCES public.keywords (keyword);

CREATE TABLE IF NOT EXISTS public.photos_people (
    photo_id text NOT NULL,
    person_id bigint NOT NULL,
    PRIMARY KEY(person_id)
);

ALTER TABLE IF EXISTS public.photos_people
    ADD CONSTRAINT fk_photos_people_person
    FOREIGN KEY (person_id)
    REFERENCES public.people (id);

ALTER TABLE IF EXISTS public.photos_people
    ADD CONSTRAINT fk_photos_people_photo
    FOREIGN KEY (photo_id)
    REFERENCES public.photos (id);

查询需求与SQL语句 #

需要检索指定人物关联的照片，并将关键词和关联人物聚合为数组，对应的SQL语句如下：

WITH photo_details AS (                                                                                                                         
   SELECT                                                                                                                                       
      p.id                                                                                                                                        
      , p.file_name                                                                                                                              
      , p.file_path                                                                                                                              
      , p.lens_make                                                                                                                              
      , p.lens_model                                                                                                                             
      , p.lens_id                                                                                                                                
      , p.make                                                                                                                                   
      , p.model                                                                                                                                  
      , p.caption                                                                                                                                
      , p.title                                                                                                                                  
      , p.creation_date_time                                                                                                                     
      , p.width                                                                                                                                  
      , p.height                                                                                                                                
      , p.latitude                                                                                                                               
      , p.longitude                                                                                                                              
      , p.year                                                                                                                                   
      , json_agg(DISTINCT pk.keyword_keyword) FILTER (WHERE pk.keyword_keyword IS NOT NULL) AS keywords                                            
      , json_agg(DISTINCT jsonb_build_object('id', pp.person_id, 'name', pe.name)) FILTER (WHERE pp.person_id IS NOT NULL AND pe.name IS NOT NULL) AS people                                                                                                                         
   FROM                                                                                                                                          
      photos p                                                                                                                                   
         LEFT JOIN photos_keywords pk ON pk.photo_id = p.id                                                                                      
         LEFT JOIN photos_people pp ON pp.photo_id = p.id                                                                                        
         LEFT JOIN people pe ON pe.id = pp.person_id                                                                                             
   WHERE                                                                                                                                        
      p.deleted_at IS NULL                                                                                                                       
      AND (                                                                                                                                     
         1=1                                                                                                                                     
                                                                                                                                                  
         AND (                                                                                                                                 
            (ARRAY[339] IS NULL) OR                                                                                                              
            (pp.person_id = ANY(ARRAY[339]))                                                                                                     
         )                                                                                                                                        
                                                                                                                                                  
        )                                                                                                                                       
   GROUP BY                                                                                                                                      
      p.id                                                                                                                                        
   ORDER BY                                                                                                                                      
      p.creation_date_time ASC                                                                                                                   
    OFFSET 0 LIMIT 25                                                                                                                            
)                                                                                                                                                
SELECT                                                                                                                                          
    id                                                                                                                                            
    , file_name                                                                                                                                  
    , file_path                                                                                                                                  
    , lens_make                                                                                                                                  
    , lens_model                                                                                                                                 
    , lens_id                                                                                                                                    
    , make                                                                                                                                       
    , model                                                                                                                                      
    , caption                                                                                                                                    
    , title                                                                                                                                      
    , creation_date_time                                                                                                                         
    , width                                                                                                                                      
    , height                                                                                                                                    
    , latitude                                                                                                                                   
    , longitude                                                                                                                                  
    , year                                                                                                                                       
    , keywords                                                                                                                                    
    , people                                                                                                                                    
FROM                                                                                                                                             
    photo_details                                                                                                                                
ORDER BY                                                                                                                                         
    creation_date_time, CONCAT(title, file_name)                                                                                                 

参数填充代码 #

人物筛选条件通过以下Go代码动态填充：

if len(trimmedPeople) > 0 {
    q := strings.Join(slices.Map(trimmedPeople, func(input uint, index int) string {
        return "?"
    }), ", ")

    s.WriteString(`
     AND (
        ARRAY[` + q + `] IS NULL OR
        pp.person_id = ANY(ARRAY[` + q + `])
     )
  `)

    for _, personID := range trimmedPeople {
        params = append(params, personID)
    }
    for _, personID := range trimmedPeople {
        params = append(params, personID)
    }
}

遇到的问题 #

曾尝试直接传入trimmedPeople变量作为查询参数，但生成的条件为ARRAY[(339)]，格式错误。现在执行查询时出现如下错误：

time=2025-01-10T10:19:46.612-06:00 level=ERROR msg="could not load photos in GetSearchPhotos" version=development error="could not query for photos in searchPhotos: ERROR: operator does not exist: bigint = text (SQLSTATE 42883)"

该查询直接在PostgreSQL中执行无问题，请问GORM是否不支持数组参数？

问题分析与解决 #

错误原因 #

报错核心是类型不匹配：手动拼接SQL时重复传递uint类型的personID参数，GORM将其绑定为text类型，而数据库中pp.person_id是bigint类型，导致bigint = text的运算符不存在。此外，原代码中ARRAY[?,...] IS NULL的条件毫无意义——当trimmedPeople有值时，数组不可能为NULL，完全可以去掉。

正确实现方式 #

GORM完全支持PostgreSQL数组参数，只需直接传递切片作为参数，无需手动拼接数组元素的占位符。修改后的参数填充代码如下：

import "slices"

// ...

if len(trimmedPeople) > 0 {
    // 将uint类型的ID转换为int64，匹配数据库bigint类型
    personIDs := slices.Map(trimmedPeople, func(id uint) int64 {
        return int64(id)
    })

    s.WriteString(`
     AND pp.person_id = ANY(?)
    `)
    params = append(params, personIDs)
}

说明 #

1. 类型匹配：将uint类型的ID转换为int64，确保与数据库bigint类型一致，避免类型转换错误。
2. 简化条件：去掉冗余的ARRAY[...] IS NULL判断，因为当trimmedPeople非空时，这个条件永远为假。
3. 正确传参：直接传递切片personIDs作为单个参数，GORM会自动将其转换为PostgreSQL支持的数组格式（如ARRAY[339]），不会出现ARRAY[(339)]的格式错误。

内容的提问来源于stack exchange，提问作者Adam Presley