SQL ARRAY

Internal

• SQL Data Types
• PostgreSQL Arrays

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