0

I have a list of of 954 values that I tried to include in a where clause using the IN function, but I get the following error.

ERROR: cannot pass more than 100 arguments to a function LINE 9: and cm.course_id ('I3133-PHYS-49990-1201-1YR-040867', ^ SQL state: 54023 Character: 332

I tried using LIMIT and OFFSET to limit the results to less than 100, but that didn't work for obvious reasons.

I don't want to run my query 10 times, changing the values each time, so is there a different approach that I can use?

thanks

Andrew Stevenson
  • 578
  • 1
  • 9
  • 23
  • Which programming language are you using? Because there is no real limit what you can put into an IN clause (e.g. `in (1,2,3,....., 1000)`). But e.g. the JDBC driver doesn't support more than 100 _parameters_ e.g. `in (?,?,?,...?)` –  Nov 17 '21 at 15:39
  • You need to rethink your query. Are the values you are filtering in another table? Then use a `SELECT` statement instead of a list of values. If you really insist on using values, then maybe `OR`ing the `IN` values together might work? (i.e. `WHERE couse_id IN (1...100) OR course_id IN (101...200)`, etc... – Bobort Nov 17 '21 at 16:14
  • 1
    Hard to say without seeing the actual query, but from the error I don't see `IN` . I see `cm.course_id ('I3133-PHYS-49990-1201-1YR-040867', ^ SQL state: 54023 Character: 332 ` where it should be `cm.course_id IN (...`. Per @Bobort I would say pull from a table, maybe even a temp one that just lasts the session. – Adrian Klaver Nov 17 '21 at 16:45
  • Hi guys, Deepest apologies. I took another look at my query with the intention of including it in my original question and found that I hadn't included the function IN, due to a copy an paste error. My query does work. Sorry for wasted your time. :( – Andrew Stevenson Nov 18 '21 at 09:38
  • Please explain to me, what is type of your 954 values and where do you get these values? from table? or? if you can write a sample query – Ramin Faracov Nov 19 '21 at 00:58

0 Answers0