How to check database collation

Print the table definition with sqlite3 shop.db ".schema users" and read the COLLATE clause on each text column. A column with no clause uses BINARY, where 'ann' and 'ANN' are different values. Then compare two strings that differ only by case and read the answer the database gives.

Why check this

Collation decides whether two strings that look alike are the same value. Check it when a migration creates or rebuilds a text column, and before sign-off on any feature that matches a name the user typed: login, search, tag deduplication, coupon codes.

The failure it prevents is two accounts. A unique index on a BINARY column accepts ann and ANN as separate rows, the login form finds whichever the query collation matches, and support ends up looking at a customer whose order history is split across two user ids. Step 5 produces that pair on purpose.

Every command here runs against SQLite 3.50.6, the only database engine on this machine. PostgreSQL collations and MySQL utf8mb4_0900_ai_ci behave differently, and neither was verified here, so this page does not describe them.

Prerequisites

• The sqlite3 shell. See the datatype and collation reference.
• A fixture. Save it as users.sql and build the database with sqlite3 users.db < users.sql. Load it from the file rather than pasting the rows as a command line argument, for the reason step 2 gives.

CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  email    TEXT NOT NULL COLLATE NOCASE,
  username TEXT NOT NULL,
  city     TEXT NOT NULL COLLATE NOCASE
);

CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

INSERT INTO users(id, email, username, city) VALUES (1, 'Ann@Example.com', 'ann', 'Kyiv');
INSERT INTO users(id, email, username, city) VALUES (2, 'rene@example.com', 'rené', 'Orléans');

• SQLite ships three collations: BINARY, NOCASE and RTRIM. PRAGMA collation_list; prints the ones the current build registered.

Steps

1. 1

   Step 1.

   Print the declaration and read the COLLATE clauses.

   sqlite3 users.db ".schema users"
   

   CREATE TABLE users (
   id       INTEGER PRIMARY KEY,
   email    TEXT NOT NULL COLLATE NOCASE,
   username TEXT NOT NULL,
   city     TEXT NOT NULL COLLATE NOCASE
   );
   CREATE UNIQUE INDEX idx_users_email ON users(email);
   CREATE UNIQUE INDEX idx_users_username ON users(username);

   email and city are NOCASE. username has no clause, so it is BINARY. PRAGMA table_info(users) will not tell you this: it has no column for collation, and it prints the same TEXT for all three.

2. 2

   Step 2.

   Before testing a non-ASCII string, check that the shell delivers it unchanged.

   sqlite3 users.db "SELECT hex('é'), hex('É'), length('é');"
   

   65|45|1

   65 is the hex of ASCII e and 45 is ASCII E. The accent was removed before SQLite saw the argument. On this Windows shell a non-ASCII literal passed on the command line arrives transliterated, so a collation test written that way is an ASCII test wearing an accent. Read the value from a file or build it with char() instead.

3. 3

   Step 3.

   Compare a case pair inside SQL, using code points so the shell cannot touch them.

   sqlite3 -header -column users.db "SELECT char(97)=char(65) COLLATE NOCASE AS ascii_a, char(233)=char(201) COLLATE NOCASE AS e_acute, char(1080)=char(1048) COLLATE NOCASE AS cyrillic_i;"
   

   ascii_a  e_acute  cyrillic_i
   -------  -------  ----------
   1        0        0

   a equals A under NOCASE, and é does not equal É. NOCASE folds the 26 ASCII letters and nothing else, so it is case insensitive for ann and case sensitive for rené.

4. 4

   Step 4.

   Insert a case variant of an existing address into the NOCASE unique index.

   sqlite3 users.db "INSERT INTO users(id,email,username,city) VALUES (3,'ANN@EXAMPLE.COM','ann2','Kyiv');"
   

   Error: stepping, UNIQUE constraint failed: users.email (19)

   The index inherits the column collation, so it treats the two spellings as one key and rejects the second. This is the behaviour you want on an email column.

5. 5

   Step 5.

   Do the same to the BINARY column and see the duplicate arrive.

   sqlite3 users.db "INSERT INTO users(id,email,username,city) VALUES (4,'ann2@example.com','ANN','Kyiv'); SELECT id, username FROM users WHERE username COLLATE NOCASE = 'ann';"
   

   4|ANN
   1|ann

   Two rows, two user ids, one person. The index did its job: under BINARY these are different values. The defect is in the declaration.

6. 6

   Step 6.

   Check what happens to the index when the query asks for a different collation.

   sqlite3 users.db "EXPLAIN QUERY PLAN SELECT id FROM users WHERE username = 'ann' COLLATE NOCASE;"
   

   QUERY PLAN
   `--SCAN users USING COVERING INDEX idx_users_username

   Without the COLLATE NOCASE the same query plans as SEARCH users USING COVERING INDEX idx_users_username (username=?). An index is ordered by its own collation, so a comparison in another one cannot seek into it and reads every row instead.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | No COLLATE in the column definition | The column is BINARY, case sensitive | Decide whether the feature wants that, then fix it in a migration, not in the query | | COLLATE NOCASE on a text column | Case insensitive for ASCII letters only | Check the non-ASCII names in the table separately. Step 3 is the test | | 1 from a case pair, 0 from an accented pair | Confirmed ASCII-only folding | Any uniqueness promise you made to users holds for ASCII names and not for the rest | | SCAN where you expected SEARCH | The query collation differs from the index collation | Match them, or add an index declared with the collation the query uses | | Two rows differing only by case in a unique column | The index and the column are both BINARY | Merge the rows first, then rebuild the column as NOCASE. The rebuild fails while duplicates exist |

Common mistakes

What to check next

• How to check table schema: the rest of what the declaration holds and what each pragma leaves out.
• How to test unique constraint: steps 4 and 5 from the other side, once the collation is settled.
• How to check if a table has an index: step 6 turns on which collation the index was built with.
• How to test unicode input: the names that step 3 shows NOCASE does not fold.
• How to check duplicate rows in sql: find the pairs already in the table before you change the column.

FAQ

How to check if data is case sensitive?

Compare a value with its own uppercase form and read the answer: SELECT 'ann' = 'ANN' AS same;. A 0 means the comparison is case sensitive. Run it against the column as well, because the column collation applies when one side is a column reference.

How to check case sensitive in SQL without changing the data?

Every step here reads. Step 3 compares two literals and touches no row, and step 6 only prints a plan. Steps 4 and 5 write, so run them against a copy of the file, which is what the prerequisites ask for.

Which collation does a comparison actually use?

An explicit COLLATE wins, left operand first. Otherwise the left column's collation applies. With a NOCASE column on the left and a BINARY one on the right the comparison returns 1, and swapping the two operands returns 0 on the same row.

Can I change a column collation in place?

Not with ALTER TABLE in SQLite. The column is rebuilt: create the new table with the collation, copy the rows, drop the old table, rename. Duplicates that were legal under BINARY will block the copy, so deduplicate first.

Does the database have one collation for everything?

No. In SQLite collation is a property of a column, an index or a single comparison, and there is no database-wide setting to read. Other engines do have a server, database and column default, and that hierarchy was not verified here.

Verified