PostgreSQL: 'ERROR: column "firstname" does not exist' (42703) on a mixed-case column

ERROR: column "firstname" does not exist with HINT: Perhaps you meant to reference the column "customers.FirstName". — the column was created with a quoted, mixed-case name, and your query names it without quotes. Write "FirstName" in double quotes, exactly as it was created.

The hint already shows the real name, capitals included. Typing FirstName without quotes does not help, because PostgreSQL lowers it to firstname before it looks. Quote the name in every query, or rename the column to lower case once and stop quoting.

The error

$ psql -U postgres -d shop -c 'SELECT FirstName, LastName FROM customers;'
ERROR:  column "firstname" does not exist
LINE 1: SELECT FirstName, LastName FROM customers;
               ^
HINT:  Perhaps you meant to reference the column "customers.FirstName".

With -v VERBOSITY=verbose psql adds the code, 42703. A WHERE Email = ... clause failed the same way, with column "email" does not exist and a hint naming customers.Email. From .NET, Npgsql reported Npgsql.PostgresException: 42703: column "firstname" does not exist with POSITION: 8, and the hint was not part of that message.

Why it happens

PostgreSQL folds every unquoted identifier to lower case and takes a quoted one literally. The table here was created with "FirstName" text NOT NULL, so the stored name has two capitals, and \d customers lists it as FirstName. The query asks for firstname, a different name, so code 42703 ("undefined column") follows. SQL Server developers meet this most, since a default SQL Server collation treats both spellings as one column.

The usual source of such names is EF Core. With the Npgsql provider and no naming configuration, the model's create script came out like this:

CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "FirstName" text NOT NULL,
    "LastName" text NOT NULL,
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);

EF Core's own queries quote every name and work. The error appears when you open psql, a reporting tool or a raw SQL string and type the names as you see them.

The fix

$ psql -U postgres -d shop -c 'SELECT "FirstName", "LastName" FROM customers;'
 FirstName | LastName
-----------+----------
 Maria     | Garcia
 David     | Chen
(2 rows)

Quoting must match exactly: "firstname" would fail again. If you control the schema, lower-case names end the problem for every tool:

$ psql -U postgres -d shop -c 'ALTER TABLE customers RENAME COLUMN "FirstName" TO first_name;' -c 'ALTER TABLE customers RENAME COLUMN "LastName" TO last_name;' -c 'ALTER TABLE customers RENAME COLUMN "Email" TO email;'
ALTER TABLE
ALTER TABLE
ALTER TABLE
$ psql -U postgres -d shop -c 'SELECT First_Name, LAST_NAME FROM customers;'
 first_name | last_name
------------+-----------
 Maria      | Garcia
 David      | Chen
(2 rows)

Unquoted, any spelling now works. In an EF Core project, rename in the model rather than by hand, so the mapping and the database stay in step: set the names with HasColumnName, or consider the EFCore.NamingConventions package for snake_case names, before the first migration if you can.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build) on Docker 29.9.2 under Windows 11, database shop, table customers created with three quoted mixed-case columns and two fictional rows. The commands ran inside the container from a script copied in with docker cp, because Windows PowerShell 5.1 strips double quotes from arguments passed to native programs. The create script came from GenerateCreateScript() in a .NET 10 console app with Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3. The names fix6-pg18 and shop are this test's own.

Frequently asked

Why does PostgreSQL say column does not exist when the column exists?

Most often the column was created with a quoted mixed-case name such as "FirstName" and the query uses it without quotes. PostgreSQL folds unquoted names to lower case, looks for firstname and does not find it. The HINT line names the real column.

Are PostgreSQL column names case sensitive?

Quoted names are stored exactly as written and must be quoted the same way every time. Unquoted names are folded to lower case, so after renaming the columns to first_name and last_name, First_Name and LAST_NAME found them in this test.

How do I query EF Core columns in psql?

Put each table and column name in double quotes exactly as EF Core created it, for example SELECT "FirstName" FROM "Customers". With the Npgsql provider and no naming configuration, EF Core generated quoted mixed-case names for both in this test.

More decoded errors in the Fixes category. The same rule hits table names, with no hint at all: relation does not exist (42P01).