SQL ARRAY: Difference between revisions
Jump to navigation
Jump to search
(→Insert) |
(→Insert) |
||
| Line 17: | Line 17: | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
INSERT INTO some_table (some_column, ...) VALUES (ARRAY['string1', 'string2', ...], ...) | INSERT INTO some_table (some_column, ...) VALUES (ARRAY['string1', 'string2', ...], ...) | ||
</syntaxhighlight> | |||
=Search in an Array= | |||
<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: | |||
<syntaxhighlight lang='sql'> | |||
... WHERE ANY(some_table.some_column) = 'value_we_search_for' | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 23:31, 24 May 2024
Internal
Overview
Declaration
SQL declaration for unidimensional arrays:
CREATE TABLE some_table (
some_column text ARRAY
...
)
ARRAY can be declared for any data types.
Insert
INSERT INTO some_table (some_column, ...) VALUES (ARRAY['string1', 'string2', ...], ...)
Search in an Array
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. ⚠️ Important: 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'
Implementation-Specific Details
| PostgreSQL | MySQL |