Fix "column does not exist"

ERROR: column "something" does not exist is the most common PostgreSQL error for beginners, and it has five usual causes. Check them in this order. Most of the time it is one of the first three.

Set up a table to break

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  city        text
);
INSERT INTO customers (name, city) VALUES ('Ana Reyes', 'Manila'), ('Ben Cruz', 'Cebu');

CREATE TABLE
INSERT 0 2

Cause 1: a typo in the column name

SELECT nme FROM customers;

ERROR:  column "nme" does not exist
LINE 1: SELECT nme FROM customers;
               ^
HINT:  Perhaps you meant to reference the column "customers.name".

Read the HINT first. PostgreSQL compares your word with the real columns and suggests the closest one. Run \d customers to see the real names, then fix the spelling.

Cause 2: double quotes around text

Single quotes are for text, and double quotes are for names. Write a word you meant as text in double quotes and PostgreSQL looks for a column called that.

SELECT name FROM customers WHERE city = "Cebu";

ERROR:  column "Cebu" does not exist
LINE 1: SELECT name FROM customers WHERE city = "Cebu";
                                                ^

The column it could not find is Cebu, which is your value. Change "Cebu" to 'Cebu'.

Cause 3: using an alias in WHERE

WHERE is evaluated before the SELECT list, so a name you give a column with AS is not available there yet.

SELECT name AS customer FROM customers WHERE customer = 'Ben Cruz';

ERROR:  column "customer" does not exist
LINE 1: SELECT name AS customer FROM customers WHERE customer = 'Ben...
                                                     ^
HINT:  Perhaps you meant to reference the column "customers.customer_id".

Use the real column in WHERE. ORDER BY runs after the SELECT list, so it can use the alias.

SELECT name AS customer FROM customers WHERE name = 'Ben Cruz';

 customer
----------
 Ben Cruz
(1 row)

Cause 4: a column created with quotes

If a table was created with a quoted name that has capital letters, the name is case sensitive for good. Unquoted names are folded to lower case, so they no longer match.

CREATE TABLE products ("Name" text, "Price" numeric(8,2));
INSERT INTO products VALUES ('Mug', 6.50);
SELECT name FROM products;

CREATE TABLE
INSERT 0 1
ERROR:  column "name" does not exist
LINE 1: SELECT name FROM products;
               ^
HINT:  Perhaps you meant to reference the column "products.Name".

The column is Name with a capital N. Either quote it everywhere, as in SELECT "Name" FROM products, or recreate the table with plain lower-case names. The second is better, because you never have to quote again.

Cause 5: the column belongs to another table

In a join, the column may exist in a table that is not in FROM.

SELECT c.name, o.status FROM customers AS c;

ERROR:  missing FROM-clause entry for table "o"
LINE 1: SELECT c.name, o.status FROM customers AS c;
                       ^

The table alias o was never introduced, so PostgreSQL reports a missing FROM-clause entry. Add the join, or take the column out.

Quick check

1. Read the caret and the HINT in the error.
2. Run \d table and compare spelling and case.
3. Look for double quotes around anything that should be text.
4. Look for an alias used in WHERE.
5. Check that every table in the SELECT list is in FROM.

Related course: SQL for Beginners.