REVERSE en PostgreSQL: invertir cadenas, busqueda por sufijo y palindromos

REVERSE — это скалярная строковая функция, которая возвращает строку, прочитанную задом наперёд. Звучит как игрушка, но именно она превращает поиск «заканчивается на» в индексируемый, проверяет палиндромы одним выражением и аккуратно переворачивает разделённые списки.

Главная ценность REVERSE — превращать «конец строки» в «начало». B-tree индекс умеет искать только по префиксу, поэтому LIKE '%@gmail.com' всегда читает всю таблицу. Развернув значение, вы переносите окончание в начало, и тот же поиск становится префиксным, а значит индексируемым. Тот же приём даёт доступ к последнему сегменту хоста или пути: разворачиваете строку, берёте первый кусок до разделителя, разворачиваете обратно. Если разворот участвует в фильтре или ключе индекса, выражение в WHERE должно дословно совпадать с выражением в индексе — иначе планировщик его не подхватит.

Синтаксис и базовый пример

Сигнатура предельно простая: REVERSE(string). Функция работает посимвольно и возвращает текст того же типа.

SELECT REVERSE('postgres') AS flipped;
-- 'sergtsop'

SELECT id, name, REVERSE(name) AS name_rev
FROM users;

Несколько деталей, которые стоит держать в голове:

• REVERSE работает на уровне символов, а не байтов, и корректно переворачивает многобайтные символы UTF-8.
• NULL на входе даёт NULL на выходе — отдельной обработки не требуется.
• Результат детерминирован и не зависит от collation: переставляются именно символы.

Индекс по суффиксу для поиска «заканчивается на»

Классическая боль: WHERE email LIKE '%@gmail.com' не использует обычный B-tree индекс, потому что шаблон начинается с %. Трюк в том, чтобы развернуть строку — тогда «суффикс» становится «префиксом», а префиксный поиск индексируется.

CREATE INDEX idx_users_email_rev
ON users (REVERSE(email) text_pattern_ops);

-- ends-with becomes a prefix scan on the reversed value
SELECT id, email
FROM users
WHERE REVERSE(email) LIKE REVERSE('%@gmail.com');

После REVERSE шаблон '%@gmail.com' превращается в 'moc.liamg@%', то есть в анкоренный префикс moc.liamg@, и планировщик может пройтись по функциональному индексу.

• Класс операторов text_pattern_ops обязателен для LIKE-префиксов вне локали C.
• Выражение в WHERE должно дословно совпадать с выражением в индексе, иначе индекс не подхватится.
• Альтернатива без REVERSE — email LIKE '%@gmail.com' плюс расширение pg_trgm с GIN-индексом, но для строгого «ends-with» развёрнутый префикс точнее и легче.

Ловушка: индекс по REVERSE(email) ускоряет только поиск по окончанию. Обычный email LIKE 'john%' (начинается с) он не покрывает — для него нужен отдельный индекс по самому столбцу.

Проверка палиндромов

Палиндром — строка, равная своему отражению. С REVERSE это одно выражение, без процедур и циклов.

SELECT name
FROM users
WHERE LOWER(name) = REVERSE(LOWER(name));

LOWER здесь убирает влияние регистра, так что Anna тоже считается палиндромом. Если нужно игнорировать пробелы и знаки, сначала почистите строку:

SELECT name,
       regexp_replace(LOWER(name), '[^a-z0-9]', '', 'g') AS norm
FROM users
WHERE regexp_replace(LOWER(name), '[^a-z0-9]', '', 'g')
    = REVERSE(regexp_replace(LOWER(name), '[^a-z0-9]', '', 'g'));

Разворот списка с разделителем

REVERSE переворачивает символы, а не элементы. Чтобы перевернуть список 'a,b,c' в 'c,b,a', его нужно разбить, изменить порядок элементов и снова склеить. В PostgreSQL читаемый путь — string_to_array, затем unnest с WITH ORDINALITY, сортировка по порядковому номеру по убыванию и сборка обратно через string_agg.

-- reverse the ELEMENTS of a delimited list, not the characters
SELECT string_agg(part, ',') AS reversed_list
FROM (
    SELECT part, ord
    FROM unnest(string_to_array('mon,tue,wed', ',')) WITH ORDINALITY AS t(part, ord)
    ORDER BY ord DESC
) s;
-- 'wed,tue,mon'

Если же нужен конкретный элемент с конца — например, домен верхнего уровня из a.b.com — пригодится связка с SPLIT_PART поверх развёрнутой строки:

-- last segment of a dotted host, taken as the first segment after REVERSE
SELECT REVERSE(SPLIT_PART(REVERSE('mail.corp.example.com'), '.', 1)) AS tld;
-- 'com'

Здесь мы разворачиваем строку, берём первый сегмент (он же последний в оригинале) и разворачиваем обратно — типичный приём «индексация с конца».

Мультибайт и различия движков

REVERSE в PostgreSQL безопасен для Unicode: он оперирует кодовыми точками, а не байтами. Но есть тонкости, общие для всех СУБД:

• Составные графемы (буква плюс комбинируемый акцент, эмодзи с модификаторами) могут «развалиться» при развороте, потому что переставляются кодовые точки, а не графемы. Для текста с диакритикой это редкая, но реальная ловушка.
• В MySQL функция тоже называется REVERSE и многобайтно-безопасна при UTF-8-наборе; убедитесь, что столбец в utf8mb4, иначе эмодзи режутся ещё до разворота.
• В ClickHouse есть и байтовый reverse, и reverseUTF8 — для строк с не-ASCII берите именно reverseUTF8, иначе получите битую последовательность.

Расхождения между движками всплывают именно на не-ASCII строках, поэтому при переносе суффиксного индекса или проверки палиндрома прогоните REVERSE на наборе с эмодзи, диакритикой и пустой строкой, а не только на латинице. В PostgreSQL функциональный индекс по REVERSE(email) работает, пока выражение совпадает дословно; в MySQL и ClickHouse такого функционального индекса нет, и развёрнутое значение придётся хранить в отдельном вычисляемом столбце, чтобы поиск по окончанию оставался индексируемым.

Когда нужен индексируемый «ends-with», предсказуемая проверка палиндрома или доступ к элементу с конца, REVERSE остаётся самым прямым инструментом — просто помните, что он переставляет символы, а не графемы и не элементы списка.