SQL Case Sensitivity: Why Does Your Query Work Even If You Change Letter Case?

Have you ever written an SQL query with a column name in UPPERCASE, and it still worked fine, even though the column was originally created in lowercase? 🤔

If you’ve been puzzled by this, you’re not alone! Let’s dive deep into how SQL databases handle case sensitivity for table names, column names, and JSON fields — so you can avoid unexpected errors and write robust queries.

1️⃣ Case Sensitivity in SQL: A Quick Overview

Case sensitivity in SQL depends on three key factors:
✅ The database engine (MySQL, PostgreSQL, etc.)
✅ The operating system (Windows vs. Linux)
✅ Whether identifiers are quoted or unquoted

Most relational databases follow these rules:

🔹 Table and Column Names → Case sensitivity varies based on the database system.
🔹 String Values in Queries → Always case-sensitive.
🔹 Keywords & Functions (e.g., SELECT, WHERE) → Always case-insensitive.

Now, let’s see how different databases handle case sensitivity in their own way.

2️) PostgreSQL: A Different Story! Case Sensitivity Matters!

PostgreSQL treats unquoted identifiers as lowercase, no matter how you write them.

Example:

CREATE TABLE Players (id SERIAL, Name VARCHAR(100));

🚀 How PostgreSQL Interprets This:

• players → ✅ Works, because PostgreSQL auto-lowers unquoted identifiers.
• Players → ❌ Error! PostgreSQL expects lowercase.

🔹 If you create a column using double quotes ("Name"), PostgreSQL stores it exactly as-is. But now, you must always reference it with quotes:

SELECT Name FROM players;  -- ❌ Error
SELECT "Name" FROM players;  -- ✅ Works

💡 Pro Tip: Avoid using quotes unless absolutely necessary! Stick to lowercase for consistency.

3) JSON Fields & Operators: Why ->> Still Works

Many databases (including PostgreSQL & MySQL) support JSON fields, which you can query using operators like ->> (to extract values as text).

Example (PostgreSQL JSON Query):

SELECT player ->> 'Balance' FROM games;

👉 Even if the JSON key is written as 'Player', this query might still work, depending on how JSON keys are stored.

🔹 Case Sensitivity in JSON Keys?

• If your JSON was inserted with “player”, then ->> 'Player' might fail.
• JSON keys are case-sensitive by default!

💡 Pro Tip: Always store JSON keys in lowercase ("player": "John") to avoid case mismatches!

4) The Takeaway: How to Avoid Case Sensitivity Issues

✔ Use lowercase for table & column names (avoids OS & database inconsistencies).
✔ Be mindful of case sensitivity in PostgreSQL (it’s stricter than MySQL).
✔ Avoid using quotes for column names in PostgreSQL unless necessary.
✔ For JSON fields, always store keys in lowercase.

By following these best practices, you’ll write more reliable SQL queries and avoid frustrating errors! 🚀

💬 Did you ever get stuck due to case sensitivity in SQL? Drop your experience in the comments below! 👇😊