admin管理员组文章数量:1123013
I'm using Go+GORM for a project. I have a DB structure that looks like this:
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);
I have the following query to retrieve photos filtered by a person, with keywords and linked people aggregated into an array in each row.
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)
The values for the person filter in the WHERE clause are populated with this code:
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)
}
}
At one point I tried passing the variable trimmedPeople directly as a query parameter, but that would cause the filter to look like:
ARRAY[(339)]
Which is wrong. The problem is I see this error:
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 phot
in searchPhotos: ERROR: operator does not exist: bigint = text (SQLSTATE 42883)"
The above query can be executed in Postgres directly without issue. Does GORM not support array parameters?
I'm using Go+GORM for a project. I have a DB structure that looks like this:
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);
I have the following query to retrieve photos filtered by a person, with keywords and linked people aggregated into an array in each row.
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)
The values for the person filter in the WHERE clause are populated with this code:
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)
}
}
At one point I tried passing the variable trimmedPeople directly as a query parameter, but that would cause the filter to look like:
ARRAY[(339)]
Which is wrong. The problem is I see this error:
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 phot
in searchPhotos: ERROR: operator does not exist: bigint = text (SQLSTATE 42883)"
The above query can be executed in Postgres directly without issue. Does GORM not support array parameters?
Share Improve this question asked 1 hour ago Adam PresleyAdam Presley 5374 silver badges4 bronze badges 2 |1 Answer
Reset to default 0Use a type cast on the array.
When you use pp.person_id = ANY(ARRAY[339]) directly in SQL, then pg knows that 339 is an untyped integer that can be safely converted to bigint, which is the type of person_id (the comparison's LHS expression), and therefore the conversion is done implicitly.
When you use pp.person_id = ANY(ARRAY[?]) in you query, and the data is sent separately, then pg doesn't know how to convert the RHS expression so that the comparison work, therefore you'll need to explicitly convert the array.
e.g.
pp.person_id = ANY(ARRAY[` + q + `]::int8[])
本文标签： postgresqlGORM error quotoperator does not exist biginttextquotStack Overflow
版权声明：本文标题：postgresql - GORM error: "operator does not exist: bigint = text" - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736540784a1944384.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
photos_people ppjoins by pp.photo_id (a text) to p.id (a text also), then I join in people by id (a bigint) to pp.person_id (a bigint). – Adam Presley Commented 52 mins ago