REVERSE in PostgreSQL: Flip Strings, Suffix Lookups, and Palindrome Checks

REVERSE is a scalar string function that returns a string read back to front. It sounds like a toy, yet it is exactly what turns an "ends with" search into an indexable one, checks palindromes in a single expression, and cleanly flips delimited lists.

Syntax and a basic example

The signature is as simple as it gets: REVERSE(string). It works character by character and returns text of the same type.

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

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

A few behaviors worth keeping in mind:

• REVERSE operates on characters, not bytes, and correctly flips multibyte UTF-8 characters.
• NULL in yields NULL out, so no special handling is needed.
• The result is deterministic and collation-independent: it is the characters that get reordered.

A suffix index for "ends with" lookups

A classic pain point: WHERE email LIKE '%@gmail.com' cannot use a plain B-tree index, because the pattern starts with %. The trick is to reverse the string so the "suffix" becomes a "prefix," and prefix search is indexable.

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');

After REVERSE, the pattern '%@gmail.com' becomes 'moc.liamg@%', an anchored prefix moc.liamg@, so the planner can scan the expression index.

• The text_pattern_ops operator class is required for LIKE prefixes outside the C locale.
• The WHERE expression must match the indexed expression verbatim, or the index will not be picked.
• An alternative without REVERSE is email LIKE '%@gmail.com' plus the pg_trgm extension with a GIN index, but for a strict ends-with the reversed prefix is sharper and lighter.

Gotcha: an index on REVERSE(email) only speeds up ends-with lookups. It does not cover a normal email LIKE 'john%' (starts-with) query, which needs its own index on the raw column.

Palindrome checks

A palindrome is a string equal to its mirror image. With REVERSE that is one expression, with no procedures or loops.

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

LOWER here removes case sensitivity, so Anna counts as a palindrome. To ignore spaces and punctuation, normalize the string first:

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'));

Reversing a delimited list

REVERSE flips characters, not elements. To turn the list 'a,b,c' into 'c,b,a', split it, reverse the order, and join it back. In PostgreSQL the readable way is string_to_array plus unnest ordered descending, then 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'

When you only need one element from the end, like the top-level domain of a.b.com, pair REVERSE with 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'

Here we reverse the string, take the first segment (the last one in the original), and reverse it back. This is the canonical "index from the end" move.

Multibyte safety and engine differences

REVERSE in PostgreSQL is Unicode-safe: it operates on code points, not bytes. But some caveats apply across every engine:

• Composite graphemes (a letter plus a combining accent, or an emoji with modifiers) can break when reversed, because code points are reordered, not graphemes. For text with diacritics this is a rare but real trap.
• In MySQL the function is also REVERSE and is multibyte-safe under a UTF-8 charset; make sure the column is utf8mb4, or emojis are mangled before reversal even starts.
• In ClickHouse there is both a byte-level reverse and a reverseUTF8 - for non-ASCII strings use reverseUTF8, or you get a broken byte sequence.

When you need an indexable ends-with, a predictable palindrome check, or access to an element from the end, REVERSE stays the most direct tool - just remember it reorders characters, not graphemes or list elements.