PostgreSQL Работа с массивами: Как найти запись по значению массива хранящегося в колонке таблицы?

В этой заметке я поделюсь как удобно хранить некий набор данных в виде массива в базе данных. А самое главное вы сможете использовать запросы для поска строк в массиве которых содержится нужное вам значение.

В нашем примере будет таблица с специалистами который говорят на разных языках, и один специалист может владеть сразу несколькими языками которые будут хранится в виде массива в колонке languages. Соответственно в результатах поиска мы сможем показать всех специалистов которые говорят например на немецком.

Создаем таблицу

Давайте для начала создадим таблицу с некоторым набором полей и нашим массивом:

CREATE TABLE professionals (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    languages TEXT[] NOT NULL // это наш массив, указвается ключом []
);

Наполняем таблицу

INSERT INTO professionals (name, languages) 
VALUES 
('John', ARRAY['SE', 'GB', 'DE']), 
('Emily', ARRAY['FR', 'GB']), 
('David', ARRAY['US', 'DE']),
('Lisa', ARRAY['IT', 'FR', 'DE']);

Обратите внимание на формат вставки данных ARRAY['IT', 'FR', 'DE'] это и есть наш массив по которому мы сможем искать по типу Where In Array.

Поиск по вхождению в массив

Теперь самое интересное. Допустим, вы хотите найти всех, кто говорит по немецки («DE»). Делаем запрос:

SELECT * FROM professionals WHERE 'DE' = ANY (languages);

Вот и вся сила магии PostgreSQL!

Однако помните, что с большой силой приходит и большая ответственность. ARRAY - это очень удобное хранилище данных, но при неправильном обращении может привести к нестабильной и медленной обработке запросов.

Если вы имеете дело с очень большим объемом данных или вам необходимо выполнять сложные запросы, подумайте об использовании отдельной таблицы и метода join для хранения отношений между сущностями и языками.