Конкатенация строк
Склеивание текста в PostgreSQL
Теория
Допустим у вас появилась необходимость собрать готовый текст из нескольких частей.
Например:
• показать полное имя пользователя, состоящее из имени и фамилии;
• собрать некоторую подпись вида 'Москва / gmail.com';
• сделать код товара из кусочков названия, категории и идентификатора;
• подготовить более удобный для чтения текст прямо в результате запроса.
Такое склеивание строк и называется конкатенацией. У слова «конкатенация», кстати, есть много похожих по смыслу определений: сцепление, соединение и так далее.
Так вот, в контексте PostgreSQL конкатенация — это когда мы берём несколько текстовых фрагментов и соединяем их в одну строку. Для этого есть несколько способов, а точнее сказать функций:
• функция CONCAT();
• функция CONCAT_WS();
• оператор ||.
На первый взгляд они делают одно и то же, но на практике между ними есть важная разница, особенно когда в одном из фрагментов встречается NULL.
Достаточно часто при конкатенации вы будете использовать уже знакомые вам инструменты:
• строковые функции вроде LEFT, RIGHT, UPPER;
• подстановка через COALESCE;
• условная логика через CASE;
Например:
• показать полное имя пользователя, состоящее из имени и фамилии;
• собрать некоторую подпись вида 'Москва / gmail.com';
• сделать код товара из кусочков названия, категории и идентификатора;
• подготовить более удобный для чтения текст прямо в результате запроса.
Такое склеивание строк и называется конкатенацией. У слова «конкатенация», кстати, есть много похожих по смыслу определений: сцепление, соединение и так далее.
Так вот, в контексте PostgreSQL конкатенация — это когда мы берём несколько текстовых фрагментов и соединяем их в одну строку. Для этого есть несколько способов, а точнее сказать функций:
• функция CONCAT();
• функция CONCAT_WS();
• оператор ||.
На первый взгляд они делают одно и то же, но на практике между ними есть важная разница, особенно когда в одном из фрагментов встречается NULL.
Достаточно часто при конкатенации вы будете использовать уже знакомые вам инструменты:
• строковые функции вроде LEFT, RIGHT, UPPER;
• подстановка через COALESCE;
• условная логика через CASE;
Синтаксис
SELECT CONCAT(first_name, ' ', last_name), -- соединяем имя, пробел и фамилию
CONCAT_WS(' / ', city, email), -- соединяем город и email через разделитель
first_name || ' ' || last_name -- соединяем имя и фамилию через оператор ||
FROM table_name; -- указываем таблицу, из которой берём данные
Общий замысел у всех трёх вариантов один: они помогают склеить несколько частей в одну строку.
Что важно понимать по каждому из них:
• CONCAT(part1, part2, part3) — просто соединяет части друг за другом;
• CONCAT(part1,'разделитель', part2,'разделитель', part3) — соединяет части друг за другом и между ними вставляет разделитель;
• CONCAT_WS('разделитель', part1, part2, part3) — тоже склеивает части, но автоматически вставляет между ними указанный в самом начале разделитель;
• part1 || part2 — оператор склеивания строк, делает тоже самое, но есть но...
Теперь, про то самое но:
• CONCAT в PostgreSQL спокойно переживает NULL и просто пропускает такой фрагмент;
• CONCAT_WS тоже пропускает NULL, а разделитель ставит только между реальными значениями;
• оператор || ведёт себя иначе: если одна из частей при конкатенации равна NULL, весь результат склейки станет NULL.
Это очень важная разница. Например:
• CONCAT('SQL', NULL, 'PRO') вернёт 'SQLPRO';
• 'SQL' || NULL || 'PRO' вернёт NULL.
Когда и что использовать:
• CONCAT удобно, когда фрагментов немного или когда между ними нужны разные разделители;
• CONCAT_WS особенно хорош и удобен, когда нужно склеить много частей через один и тот же разделитель;
• CONCAT_WS не подойдет, если между разными частями должны стоять разные разделители;
• || удобен для коротких и простых выражений, но с ним нужно внимательнее следить за NULL.
Ещё одна особенность PostgreSQL: CONCAT и CONCAT_WS сами умеют приводить многие значения к тексту. Поэтому, если вы склеиваете строку, число или дату, дополнительно переводить тип данных ::text часто не нужно. Но если вы хотите явно контролировать формат значения, приведение типа явным образом не будет лишним.
Как упоминалось выше в конкатенацию можно спокойно вкладывать уже знакомые функции. Например, запись CONCAT(UPPER(LEFT(city, 3)), '-', user_id) сначала возьмёт первые три буквы города, переведёт их в верхний регистр, а потом склеит с дефисом и идентификатором.
Что важно понимать по каждому из них:
• CONCAT(part1, part2, part3) — просто соединяет части друг за другом;
• CONCAT(part1,'разделитель', part2,'разделитель', part3) — соединяет части друг за другом и между ними вставляет разделитель;
• CONCAT_WS('разделитель', part1, part2, part3) — тоже склеивает части, но автоматически вставляет между ними указанный в самом начале разделитель;
• part1 || part2 — оператор склеивания строк, делает тоже самое, но есть но...
Теперь, про то самое но:
• CONCAT в PostgreSQL спокойно переживает NULL и просто пропускает такой фрагмент;
• CONCAT_WS тоже пропускает NULL, а разделитель ставит только между реальными значениями;
• оператор || ведёт себя иначе: если одна из частей при конкатенации равна NULL, весь результат склейки станет NULL.
Это очень важная разница. Например:
• CONCAT('SQL', NULL, 'PRO') вернёт 'SQLPRO';
• 'SQL' || NULL || 'PRO' вернёт NULL.
Когда и что использовать:
• CONCAT удобно, когда фрагментов немного или когда между ними нужны разные разделители;
• CONCAT_WS особенно хорош и удобен, когда нужно склеить много частей через один и тот же разделитель;
• CONCAT_WS не подойдет, если между разными частями должны стоять разные разделители;
• || удобен для коротких и простых выражений, но с ним нужно внимательнее следить за NULL.
Ещё одна особенность PostgreSQL: CONCAT и CONCAT_WS сами умеют приводить многие значения к тексту. Поэтому, если вы склеиваете строку, число или дату, дополнительно переводить тип данных ::text часто не нужно. Но если вы хотите явно контролировать формат значения, приведение типа явным образом не будет лишним.
Как упоминалось выше в конкатенацию можно спокойно вкладывать уже знакомые функции. Например, запись CONCAT(UPPER(LEFT(city, 3)), '-', user_id) сначала возьмёт первые три буквы города, переведёт их в верхний регистр, а потом склеит с дефисом и идентификатором.
Примеры
1. Собираем полное имя пользователя
Базовый сценарий для конкатенации. Берём имя, добавляем пробел и приклеиваем фамилию. В результате получаем готовое полное имя в одном столбце.
🔄 Попробуйте изменить запрос:
- • Замените пробел на ' - ' и посмотрите, как изменится результат
- • Попробуйте тот же сценарий через оператор ||
2. Склеиваем сразу несколько столбцов через один разделитель
Функция CONCAT_WS обеспечивает удобство при объединении нескольких столбцов с использованием одного разделителя. Это делает код более аккуратным по сравнению с применением CONCAT, где разделитель добавляется после каждого столбца. Кроме того, если один из фрагментов имеет значение NULL, функция не добавляет разделитель в пустое место.
🔄 Попробуйте изменить запрос:
- • Перепишите этот же запрос через обычный CONCAT и сравните, насколько запись станет длиннее
- • Добавьте в конкатенацию ещё и registration_date и посмотрите, что PostgreSQL сам приведёт дату к тексту
3. Сравниваем поведение CONCAT и оператора || при NULL
Один из самых важных примеров темы. Он показывает разницу в поведении. CONCAT пропустит NULL и всё равно соберёт строку, а оператор || в результате выдаст NULL.
🔄 Попробуйте изменить запрос:
- • Замените NULL на '-' и сравните оба результата
- • Попробуйте защитить оператор || через функцию COALESCE(NULL, '')
4. Используем CONCAT, когда разделители между частями разные
В этом примере необходим CONCAT, а вот CONCAT_WS не подойдет. Здесь между разными частями нужны разные разделители: пробел, потом квадратные скобки.
🔄 Попробуйте изменить запрос:
- • Замените квадратные скобки на круглые: '(' и ')'
- • Добавьте в конец строки ещё и email, отделив его через ' - '
5. Собираем условный шифр товара из нескольких кусков
Более интересный и сложный сценарий. Мы не просто склеиваем готовые значения, а сначала извлекаем нужные куски из разных полей, переводим их в нужный вид и только потом собираем итоговый код.
🔄 Попробуйте изменить запрос:
- • Сделайте код короче: возьмите не четыре, а три символа из product_name
- • Поменяйте разделитель '-' на '_'
6. Собираем шифр заказа через оператор || и защищаемся от NULL
Этот пример показывает дополнительный способ работать с оператором ||, если в одной из частей может быть NULL, например в столбце статусов. Мы заранее подставляем запасной текст через COALESCE, и тогда вся строка не разваливается, если вдруг там будет NULL.
🔄 Попробуйте изменить запрос:
- • Уберите функцию COALESCE и посмотрите, что произойдёт у заказов без статуса
- • Добавьте в шифр ещё и worker_id
Типичные ошибки
Считают, что CONCAT и оператор || всегда ведут себя одинаково
Нет, не всегда. При NULL это как раз не так. CONCAT спокойно пропускает пустой фрагмент, а || может вернуть NULL для всей склейки.
Забывают, что у CONCAT_WS первый аргумент — это разделитель
В записи CONCAT_WS(' / ', city, email) строка ' / ' — это не обычная часть результата, а именно разделитель между остальными значениями.
Указывают разделители вручную там, где лучше подошёл бы CONCAT_WS
Если у вас несколько частей и между ними нужен один и тот же разделитель, CONCAT_WS делает запись чище и короче.
Думают, что число или дата обязательно требуют ::text в CONCAT
Во многих обычных случаях PostgreSQL сам приведёт такие значения к тексту внутри CONCAT и CONCAT_WS. Явное приведение нужно не всегда, а только когда вы хотите жёстко контролировать формат или работаете в более чувствительном сценарии.
Забывают защититься от NULL при использовании ||
Если в одной из частей может быть NULL, можно воспользоваться функцией COALESCE. Например: first_name || ' ' || COALESCE(city, '').
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
В какой ситуации обычно особенно удобна функция CONCAT_WS?
2
Что вернёт выражение CONCAT('SQL', NULL, 'PRO')?
3
Что вернёт выражение CONCAT_WS('/', 'SQL', NULL, 'PRO')? Введите результат без кавычек.
4
Что произойдёт, если одна из частей в склейке через оператор || окажется равна NULL?
5
Какой вариант обычно уместнее, если между частями строки должны стоять разные разделители: пробел, потом скобки, потом дефис?
6
Напишите запрос к таблице workers, который выведет worker_id, first_name, last_name и столбец worker_line. В worker_line нужно склеить имя, фамилию и должность через разделитель ' | ' с помощью CONCAT_WS. Оставьте только работников из отделов 1 и 2. Отсортируйте результат по worker_id.
7
Напишите запрос к таблице orders, который выведет order_id, order_date и столбец order_label. В order_label нужно через CONCAT собрать строку вида 'Заказ #15 / user 3'. Оставьте только заказы без статуса и отсортируйте результат по order_date от более новой к более старой.
8
Что в PostgreSQL обычно верно для CONCAT и CONCAT_WS при работе с числами и датами?