Postgres Case Sensitivity Explained

Case sensitivity in PostgreSQL trips up plenty of experienced developers, because it works one way for identifiers and a different way for the data inside them. Once you know which rule applies where, the surprises go away. Here is what you need to know.

1. Identifiers (table/column names)#

• Unquoted identifiers are folded to lowercase. Postgres lowercases anything you don't put in double quotes, so the casing you typed in CREATE TABLE is not necessarily the casing stored in the catalog.

  CREATE TABLE Customer (ID int, UserName varchar(50));
  -- Actually created as: customer(id, username)
  
  SELECT * FROM customer;   -- ✅ works
  SELECT * FROM Customer;   -- ✅ works (folded to lowercase)

• Quoted identifiers preserve case and must be referenced exactly. The moment you wrap a name in double quotes, you opt out of folding, and every reference to that name has to match it character for character.

  CREATE TABLE "Customer" ("ID" int, "UserName" varchar(50));
  
  SELECT * FROM "Customer";           -- ✅ works
  SELECT * FROM customer;             -- ❌ ERROR: relation "customer" does not exist
  SELECT id FROM "Customer";          -- ❌ ERROR: column "id" does not exist

Best practice: avoid quoted identifiers. Use lowercase snake_case (e.g., order_items, created_at). It reads cleanly, and it sidesteps the whole quoting problem.

2. Strings (data values)#

• String comparisons are case-sensitive by default. This is separate from the identifier rule above. 'abc' and 'ABC' are simply different values, so an equality check on them returns false.

  SELECT 'abc' = 'ABC';  -- false
  INSERT INTO users (username) VALUES ('JohnDoe'), ('janedoe');
  SELECT * FROM users WHERE username = 'johndoe';  -- 0 rows

• Case-insensitive matching comes in a few flavors, depending on whether you want a one-off query or a column that is always insensitive.

  • Functions and operators handle the ad-hoc case:

    SELECT * FROM users WHERE lower(username) = 'johndoe';  -- ✅ finds 1 row
    SELECT * FROM users WHERE username ILIKE 'john%';       -- ✅ case-insensitive LIKE
    SELECT * FROM users WHERE username ~* '^john';          -- ✅ case-insensitive regex

  • The citext extension makes a column behave case-insensitively without rewriting every query against it:

    CREATE EXTENSION IF NOT EXISTS citext;
    CREATE TABLE users (id SERIAL, username CITEXT UNIQUE);
    SELECT * FROM users WHERE username = 'johndoe';  -- ✅ automatically case-insensitive

  • Nondeterministic collations (PostgreSQL 12+) push the same behavior down to the collation level, which also affects uniqueness and sorting:

    CREATE COLLATION case_insensitive (
        provider = icu, 
        locale = 'und-u-ks-level2', 
        deterministic = false
    );
    CREATE TABLE users (username TEXT COLLATE "case_insensitive");

3. Indexing for case-insensitive search#

Wrapping a column in lower() works, but it also defeats a plain index on that column, so the query falls back to a full scan. The fix is to index the expression you actually search on.

• Functional index to avoid full scans:

  CREATE INDEX users_name_lower_idx ON users (lower(name));
  -- Query must match the expression:
  SELECT * FROM users WHERE lower(name) = lower($1);

• For prefix searches, add text_pattern_ops so LIKE 'foo%' can use the index:

  CREATE INDEX users_name_lower_like_idx ON users (lower(name) text_pattern_ops);
  SELECT * FROM users WHERE lower(name) LIKE lower($1) || '%';

4. ORMs & migrations: common pitfalls#

• Some ORMs emit quoted identifiers, which locks you into exact casing everywhere downstream. If your ORM has a setting that generates unquoted, lowercase names, prefer it.
• Mixing quoted and unquoted names across migrations is where the "why can't it find my table?" bugs come from. Standardize on lowercase, unquoted schema objects and the inconsistency disappears.

-- Wrong: Mixing quoted and unquoted
CREATE TABLE user_accounts (user_id SERIAL);
ALTER TABLE "User_Accounts" ADD COLUMN email VARCHAR(100);  -- ❌ Fails

-- Correct: Consistent unquoted
ALTER TABLE user_accounts ADD COLUMN email VARCHAR(100);    -- ✅ Works

Quick Rules of Thumb#

1. Schema: lowercase + unquoted identifiers, always.
2. Search: use ILIKE, lower() with functional indexes, or citext.
3. Avoid quoted names unless you have a compelling reason.
4. ORMs: configure to generate lowercase, unquoted schema objects.

Stick to these and you will stay consistent, avoid the case traps, and keep your queries fast.