Menu
Is there any other way to search for a certain value in an integer[] column in Postgres?
My currently installed Postgres version does not allow the following statement:
SELECT * FROM table WHERE values *= 10;
Array examples:
'{11043,10859,10860,10710,10860,10877,10895,11251}'
'{11311,10698,10697,10710,10712,10711,10708}'
The statement should return every row where the array contains '10710'.
563
The elements of the array can be retrieved using the SELECT statement. The values of the array column can be enclosed within square brackets [] or curly braces {}. We can search for array column values using the ANY() function.
The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows.
PostgreSQL makes it less complicated for using arrays in a query and finding the length of a column using only the simple syntax array_length (column_name, int). The “array_length” in this syntax returns the length of an array of the first argument i.e., column_name, and “int” tells the dimension of the array measured.
For equality checks you can simply:
SELECT * FROM table WHERE 10 = ANY (values);
Read about ANY/SOME in the manual.
See also:
122
quickly search will be so, but you should use index gist or gin for intarray type Postgres intarray
SELECT * FROM table WHERE values @> ARRAY[10];
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
