Как менять значения «на лету», не перечисляя все колонки в SELECT

В прошлом посте я рассказывал про модификатор EXCEPT, с помощью которого можно исключить ненужные колонки из выборки. Сегодня расскажу про еще один полезный модификатор — REPLACE.

Возьмем все тот же пример: широкую таблицу, в которой 50 или более колонок. Часто бывают ситуации, когда нам нужно модифицировать значения только в одном-двух полях (например, привести email к нижнему регистру, округлить цену или захешировать данные), а все остальные поля вывести как есть.

В классических транзакционных базах, таких как PostgreSQL, мы бы вручную перечисляли в SELECT абсолютно все поля: и те, которые нужно просто вывести, и те, значения в которых нужно как-то изменить.

В ClickHouse это делается проще.

Чтобы не писать десятки колонок руками, можно использовать модификатор REPLACE. Он работает в связке со звездочкой * и позволяет вывести все поля, заменив значения только в конкретных.

Обычно мы пишем так:

SELECT
  id,
  category,
  user_id,
  -- ... перечисляем десятки остальных колонок руками ...
  LOWER(email) AS email
FROM table

С использованием REPLACE запрос становится короче:

SELECT * REPLACE (LOWER(email) AS email)
FROM table

А если нам надо и исключить колонки, и заменить значения, можно писать EXCEPT и REPLACE вместе:

SELECT * 
    EXCEPT (updated_at) 
    REPLACE (LOWER(email) AS email)
FROM table

Несколько нюансов:

1. Правила замены пишутся в круглых скобках. Синтаксис такой: (выражение AS имя_существующей_колонки).

2. Можно изменять сразу несколько колонок, перечислив их через запятую: REPLACE (LOWER(email) AS email, price * 0.85 AS price).

3. Тип данных колонки в итоговой выборке поменяется, если ваше выражение возвращает новый тип (например, вы заменили число на строку).

Про еще один модификатор расскажу в следующем посте.

Ссылка на доку.

Мои статьи по ClickHouse на Хабре.

P.S. Систематизировать знания и получить крепкую базу можно на моем бесплатном курсе «ClickHouse с нуля». А закрепить пройденный материал на его практическом продолжении «ClickHouse с нуля: практика».