Explained that the linked answers have now been corrected.

edited May 19, 2022 at 21:39

3.1k
1
30
34

I have a Postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL / IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the Postgres docs seem to only mention <, =, <= etc. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

EDIT: Since posting both of those answers have now been updated to no longer suggest that a B-tree index will work.

I have a Postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL / IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the Postgres docs seem to only mention <, =, <= etc. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

I have a Postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL / IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the Postgres docs seem to only mention <, =, <= etc. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

EDIT: Since posting both of those answers have now been updated to no longer suggest that a B-tree index will work.

format

Source Link

edited May 16, 2022 at 1:56

Erwin Brandstetter

668.6k
160
1.2k
1.3k

I have a postgresPostgres table with a column that's an array of strings. TheThe only searches that will be done using this column will be either IS NULLIS NULL /NOT NULL IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column) 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@<@ operator which checks if the elements of one array are included in the other (of which ANYANY is really the special case of a 1 element array). HoweverHowever, the answer to this question seems to suggest that ANYANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@<@ works with them).

However, the postgresPostgres docs seem to only mention <<, ==, <=<= etc.. as supported by B-tree. WhatWhat index do I need to support a query searching for rows where an array column contains a specified value?

I have a postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL/NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column) )

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the postgres docs seem to only mention <, =, <= etc.. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

I have a Postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL / IS NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column)).

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the Postgres docs seem to only mention <, =, <= etc. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

Source Link

asked May 15, 2022 at 18:42

Peter Gerdes

3.1k
1
30
34

Postgres index type for array column for ANY queries

I have a postgres table with a column that's an array of strings. The only searches that will be done using this column will be either IS NULL/NOT NULL and queries asking for rows where the array contains some value (e.g., 'vote'=ANY(T.column) )

Now, I know that a GIN index supports the <@ operator which checks if the elements of one array are included in the other (of which ANY is really the special case of a 1 element array). However, the answer to this question seems to suggest that ANY works fine with B-tree indexes as does Shana K's answer from this question (well actually she suggests <@ works with them).

However, the postgres docs seem to only mention <, =, <= etc.. as supported by B-tree. What index do I need to support a query searching for rows where an array column contains a specified value?

Collectives™ on Stack Overflow

Return to Question

Postgres index type for array column for ANY queries