Cannot use Where condition in Array columns

Question

I have a column which is of an array type. I want to use the where condition in my script, but am unable to. The unnest formula is too complex to use and I want to keep it simple here.

I have 4 columns. One of them is called box_number. It can have an array of multiple numbers. I want to search for rows where box_number contains 123.

select
*
from BOX_TABLE
where box_number is {123}

ERROR: syntax error at or near "{"

Wolfetto · Accepted Answer · 2019-09-09 14:38:55Z

4

SELECT * FROM BOX_TABLE WHERE 123 = ANY (box_number);

You check that at least one value inside the column is 123.

answered Sep 9, 2019 at 14:38

Wolfetto

1,20010 silver badges21 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2019-09-09 14:31:53Z

3

To test for equality, try

WHERE box_number = ARRAY[123]

To test if the array contains your value, use the “contains” operator &&:

WHERE box_number && ARRAY[123]

answered Sep 9, 2019 at 14:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Cannot use Where condition in Array columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related