CodingNic
Sign In Book Session

SQL Basics

Keywords and Identifiers

SQL Basics 8 min read

Keywords and Identifiers

Objectives

By the end of this lesson, you should be able to:

  • Distinguish a SQL keyword from an identifier
  • Explain PostgreSQL’s default identifier casing behavior
  • Explain when double quotes around an identifier matter

💡 Why this matters: Every query mixes SQL’s fixed vocabulary with names you chose yourself. Telling them apart, and understanding a quirk in how PostgreSQL handles casing, avoids a specific class of confusing bugs.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Keywords: SQL’s Own Vocabulary

A keyword is a word with a fixed, special meaning in SQL, SELECT, FROM, WHERE, CREATE, TABLE, and so on. Keywords aren’t names you choose, they’re part of the language itself, and PostgreSQL reserves a large set of them.

Identifiers: Names You Choose

An identifier is a name you give to something, a table name, a column name. products, product_name, and price are all identifiers in the examples so far, they’re not part of SQL’s vocabulary, they’re specific to this database’s design.

PostgreSQL Lowercases Unquoted Identifiers

Here’s the quirk: when an identifier isn’t wrapped in double quotes, PostgreSQL automatically converts it to lowercase, no matter how it was typed.

sql
SELECT PRODUCT_NAME FROM PRODUCTS;

This still works, and returns the same result as SELECT product_name FROM products;, because PostgreSQL folds PRODUCT_NAME and PRODUCTS down to lowercase before looking them up. This is exactly why table and column names throughout this course are written in lowercase from the start, to match what PostgreSQL does automatically, rather than fight it.

Double-Quoted Identifiers Are Case-Sensitive

Wrapping an identifier in double quotes turns off that automatic lowercasing, and also allows things an unquoted identifier can’t have, like spaces:

sql
SELECT product_name AS "Product Name" FROM products LIMIT 1;
text
 Product Name
----------------
 Wireless Mouse

The alias "Product Name" (covered fully in a later lesson) keeps its exact capitalization and space because it’s double-quoted. Without the quotes, Product Name would be invalid, SQL would try to read Product and Name as two separate, unquoted words.

Double quotes are for identifiers, table and column names. Single quotes are for text values, 'Notebook' is a piece of data, not a name, covered in the next lesson.

Try It

  1. Explain the difference between a keyword and an identifier, with one example of each.
  2. Would SELECT Price FROM Products; and SELECT price FROM products; return the same result in PostgreSQL? Explain why.
  3. Explain why SELECT product_name AS Product Name FROM products; (without quotes around the alias) would cause an error, while SELECT product_name AS "Product Name" FROM products; works.

Recap

  • Keywords (SELECT, FROM, WHERE) are SQL’s fixed vocabulary, identifiers (table and column names) are names you choose.
  • PostgreSQL automatically lowercases unquoted identifiers, which is why this course writes them in lowercase from the start.
  • Double-quoted identifiers preserve exact casing and allow spaces, single quotes are for text values, not names.

Next lesson: comments, literals, and expressions, the remaining pieces that show up inside almost every query.