Filter multiple columns by one condition Postgresql

Question

I have multiple fields in Postgresql table of the same type which is bigint. I would like to select values which are in the int range (-2147483647, 2147483647). I tried to do something like this but with multiple fields it does not really look good:

select * from test_table
where field1 between -2147483647 and 2147483647  
  and field2 between -2147483647 and 2147483647  
  and field3 between -2147483647 and 2147483647

How can I apply one range filtering for multiple columns?

Richard van Velzen · Accepted Answer · 2020-03-04 10:30:06Z

6

You can use an int8range combined with range operators and an array of the field values:

SELECT *
FROM test_table
WHERE int8range(-2147483647, 2147483647) @> ANY(ARRAY[field1, field2, field3])

answered Mar 4, 2020 at 10:30

Richard van Velzen

1941 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter multiple columns by one condition Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related