column does not exist

This error occurs when you reference a column name that doesn't exist in the table or when PostgreSQL interprets your identifier incorrectly.

The column does not exist error means PostgreSQL can't find the column you're referencing.

Understanding the Error

ERROR: column "username" does not exist
LINE 1: SELECT username FROM users;
               ^
HINT: Perhaps you meant to reference the column "users.user_name".

The column name is wrong or missing quotes.

Common Causes

1. Wrong Column Name

SQL

SELECT username FROM users;  -- Error: column is actually 'user_name'
SELECT user_name FROM users;  -- Works!

2. Missing Quotes on String

SQL

-- Unquoted string treated as column name
SELECT * FROM users WHERE name = John;  -- Error: looking for column 'john'
SELECT * FROM users WHERE name = 'John';  -- Works!

3. Case Sensitivity

SQL

-- Column created with quotes
CREATE TABLE users (id INT, "Name" VARCHAR(100));
SELECT name FROM users;  -- Error: looking for lowercase 'name'
SELECT "Name" FROM users;  -- Works!

4. Wrong Table Alias

SQL

SELECT u.email FROM users AS u;  -- Works
SELECT users.email FROM users AS u;  -- Error: 'users' not recognized

How to Fix It

Solution 1: Check Column Names

SQL

-- List all columns in a table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users';

-- Or in psql
\d users

Solution 2: Quote String Values

SQL

-- Wrong: unquoted string
SELECT * FROM users WHERE status = active;

-- Correct: quoted string
SELECT * FROM users WHERE status = 'active';

Solution 3: Handle Case Sensitivity

SQL

-- Use quotes for case-sensitive column names
SELECT "firstName", "lastName" FROM users;

-- Better: use lowercase names when creating tables
CREATE TABLE users (
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

Solution 4: Use Correct Table Alias

SQL

SELECT u.id, u.email
FROM users u
JOIN orders o ON u.id = o.user_id;

Solution 5: Add Missing Column

SQL

ALTER TABLE users ADD COLUMN username VARCHAR(50);

Common Mistakes

SQL

-- Missing quotes around string value
WHERE name = Alice  -- Error: column 'alice' not found
WHERE name = 'Alice'  -- Correct

-- Double quotes vs single quotes
WHERE name = "Alice"  -- Error: column '"Alice"' not found
WHERE name = 'Alice'  -- Correct

-- Backticks (MySQL syntax, not PostgreSQL)
SELECT `email` FROM users;  -- Error
SELECT email FROM users;  -- Correct

Best Practices

Use lowercase column names to avoid quote requirements
Use single quotes for string values
Double quotes only for identifiers when necessary
Consistent naming (snake_case recommended)
Always check schema when unsure about column names