Database DevSecOps

PostgreSQL's case sensitivity rules can surprise even experienced developers. Here's the concise, no-gotchas guide.

1. Identifiers (table/column names)

Unquoted identifiers are folded to lowercase.

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.

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).

2. Strings (data values)

String comparisons are case-sensitive by default.

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

Case-insensitive matching

Functions and operators:

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

citext extension:

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+):

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

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:

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, locking you into exact casing everywhere. Prefer ORM settings that generate unquoted, lowercase names.
Mixing quoted and unquoted names across migrations leads to "why can't it find my table?" bugs — standardize on lowercase, unquoted schema objects.

-- 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