LIKE Ignores Letter Case on SQLite and MySQL and Respects It on PostgreSQL

A search box that runs WHERE name LIKE 'ann%' finds Ann on SQLite, MySQL and MariaDB. On PostgreSQL it misses her, because LIKE there compares letters exactly.

SQLite’s LIKE ignores case for the letters A to Z only. MySQL and MariaDB follow the collation, which ignores case by default. PostgreSQL’s LIKE is exact; its ILIKE ignores case, and exists on PostgreSQL only.

What Each Engine Answered

The example

SELECT 'abc' LIKE 'ABC';

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT LOWER('abc') LIKE LOWER('ABC');

Write LOWER(x) LIKE LOWER(pattern), or a pattern that is already in lower case, to ignore case on all four.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: info: LIKE ignores letter case on SQLite, MySQL and MariaDB and respects it on PostgreSQL. [like-case]
    'abc' LIKE 'ABC'
    Write LOWER(x) LIKE LOWER(pattern), or a pattern that is already in lower case, to ignore case on all four.

1 problem: 1 note.

baresql rules like-case prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.