SQL ARRAY: Difference between revisions
Jump to navigation
Jump to search
(3 intermediate revisions by the same user not shown) | |||
Line 19: | Line 19: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
= | =Searching in Arrays= | ||
==Searching for One Element== | |||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
SELECT some_column FROM some_table WHERE 'value_we_search_for' = ANY(some_table.some_column) | SELECT some_column FROM some_table WHERE 'value_we_search_for' = ANY(some_table.some_column) | ||
Line 32: | Line 33: | ||
=Implementation-Specific Details= | =Implementation-Specific Details= | ||
{| class="wikitable" style="text-align: left;" | ::{| class="wikitable" style="text-align: left;" | ||
|- | |- | ||
| [[PostgreSQL_Concepts#Arrays|PostgreSQL]] || MySQL | | [[PostgreSQL_Concepts#Arrays|PostgreSQL]] || MySQL | ||
|- | |- | ||
|} | |} |
Latest revision as of 19:28, 31 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', ...], ...)
Searching in Arrays
Searching for One Element
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'
Implementation-Specific Details
PostgreSQL MySQL