Latest revision as of 19:28, 31 May 2024

Internal

SQL declaration for unidimensional arrays:

CREATE TABLE some_table (
  some_column text ARRAY
  ...
)

ARRAY can be declared for any data types.

INSERT INTO some_table (some_column, ...) VALUES (ARRAY['string1', 'string2', ...], ...)

SELECT some_column FROM some_table WHERE 'value_we_search_for' = ANY(some_table.some_column)

some_column must be a text ARRAY type. The expression above evaluates to TRUE if 'value_we_search_for' is in any of the array elements.

⚠️ The inclusion test must start with the value, it cannot start with ANY(). The following expression will produce a syntax error:

... WHERE ANY(some_table.some_column) = 'value_we_search_for'

PostgreSQL	MySQL

@@ Line 19: / Line 19: @@
 </syntaxhighlight>
-=Search in an Array=
+=Searching in Arrays=
+==Searching for One Element==
 <syntaxhighlight lang='sql'>
 SELECT some_column FROM some_table WHERE 'value_we_search_for' = ANY(some_table.some_column)
 </syntaxhighlight>
-<code>some_column</code> must be a text <code>ARRAY</code> type. The expression above evaluates to TRUE if 'value_we_search_for' is in any of the array elements. ⚠️ Important: the inclusion test '''must''' start with the value, it cannot start with ANY(...). The following expression will produce a syntax error:
+<code>some_column</code> must be a text <code>ARRAY</code> type. The expression above evaluates to TRUE if 'value_we_search_for' is in any of the array elements.
+⚠️ The inclusion test '''must''' start with the value, it cannot start with <code>ANY()</code>. The following expression will produce a syntax error:
 <syntaxhighlight lang='sql'>
 ... WHERE ANY(some_table.some_column) = 'value_we_search_for'
@@ Line 30: / Line 33: @@
 =Implementation-Specific Details=
-{| class="wikitable" style="text-align: left;"
+::{| class="wikitable" style="text-align: left;"
 |-
 | [[PostgreSQL_Concepts#Arrays|PostgreSQL]] || MySQL
 |-
 |}