PostgreSQL: unnest(array)

Моя любимая функция PostgreSQL — unnest (ссылка на документацию)! Она такая простая, но такая классная 😀

Она очень пригодится, если нужно вставить сразу несколько записей за один присест. Допустим, у одной фирмы есть несколько номеров телефонов и,чтобы не перечислять вставку этих данных через запятую, дублируя id фирмы, можно использовать unnest — элегантное решение!

Пусть у нас есть таблица telephone_numbers:

create table public.telephone_numbers (
id integer not null,
number character varying(100) not null
constraint numbers_pkey primary key (id, number))

Для фирмы с id = 5 надо вставить 5 номеров телефона: 123, 456, 789, 147, 258.

Через перечисление:

insert into telephone_numbers(id, number)
values (5, '123'), (5, '456'), (5, '789'), (5, '258'), (5, '147');

Муторно же 🙂

Используя unnest(array):

insert into telephone_numbers(id, number)
select 5, unnest(array['123', '456', '789', '258', '147']);

Функция раскладывает значения, указанные в массиве array[] на строки, таким образом, автоматически формируя вставку значений через перечисление.

Это очень удобно, когда нам необходимо вставить за раз несколько массивов данных. Тогда они будут последовательно связаны друг с другом. Например, следующая конструкция

insert into products(product_code, price)
select unnest(array[111, 222, 333, 444]), unnest(array[1.25, 0.50, 1.50, 2]);

будет эквивалентна записи:

insert into products(product_code, price) 
values (111, 1.25), (222, 0.50), (333, 1.50), (444, 2);

Если в таком перечислении будет неравное количество элементов, недостающие будут заменены на null.

Очень удобно использование unnest при формировании скрипта в коде. Например, в Qt:

QSqlQuery query;
QStringList productCodes, prices;
productCodes << "111" << "222" << "333" << "444";
prices << "1.25" << "0.50" << "1.50" << "2";
if (!query.exec(QString("insert into products(product_code, price) "
						"select unnest(array[%1]), unnest(array[%2])")
			   .arg(productCodes.join(", "), prices.join(", "))))
	qDebug() << query.lastError().text();

Перечислять массивы внутри функции можно через запятую. И вот такая инструкция

select * from 
unnest(array[1,2,3], array['a', 'b', 'c'])

превращается в такую выборку: