输入IN子句时的替代方法很多值(postgreSQL) [英] Alternative when IN clause is inputed A LOT of values (postgreSQL)

问题描述

我正在使用IN子句来检索包含某些标签的位置.为此，我只需使用

I'm using the IN clause to retrieve places that contains certain tags. For that I simply use

select .. FROM table WHERE tags IN (...)

目前，我在IN子句中提供的标签数量约为500个，但很快(在不久的将来)，数字标签可能会轻松跃升至5000个以上(甚至更多)

For now the number of tags I provide in the IN clause is around 500) but soon (in the near future) number tags will probably jump off to easily over 5000 (maybe even more)

我想查询的大小和IN子句中的数字值都存在某种限制(好奇的问题是这个值是什么?)

I would guess there is some kind of limition in both the size of the query AND in the number values in the IN clause (bonus question for curiosity what is this value?)

所以我的问题是，哪怕将来我要匹配10'000个标签，什么是可以证明未来的好的替代查询呢?

So my question is what is a good alternative query that would be future proof even if in the future I would be matching against let's say 10'000 tags ?

ps:我环顾四周，看到有人提到临时表".我从来没有用过.在我的情况下将如何使用它们?每次查询时都需要创建一个临时表吗?

ps: I have looked around and see people mentioning "temporary table". I have never used those. How will they be used in my case? Will i need to create a temp table everytime I make a query ?

谢谢， 弗朗切斯科

推荐答案

一种选择是将其加入values子句

One option is to join this to a values clause

with parms (tag) as (
  values ('tag1'), ('tag2'), ('tag3')
)
select t.*
from the_table t
  join params p on p.tag = t.tag;

这篇关于输入IN子句时的替代方法很多值(postgreSQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！