How to Resolve Postgres "column does not exist" After a Schema Change

What the error means

Postgres checks every column name in a query against the table definition before it runs anything. If one name is not there, the whole statement fails:

ERROR:  column "active" does not exist
LINE 1: SELECT id, name, email FROM users WHERE active = true
                                                ^
HINT:  Perhaps you meant to reference the column "users.is_active".

The caret shows the exact name Postgres could not find, and the HINT line often suggests the closest real column. The usual causes are:

• A migration renamed the column (active became is_active) but the application code was not updated.
• The query runs against a different database or schema than you think.
• The column was created with double quotes and mixed case ("isActive"), so it must always be quoted the same way.

Why the API returns an empty list instead of an error

This bug is hard to see when the code catches the database error and returns a default value:

@app.get("/users")
def list_users():
    try:
        cur.execute(
            "SELECT id, name, email FROM users WHERE active = true ORDER BY name"
        )
        rows = cur.fetchall()
    except psycopg2.Error:
        rows = []          # the real error is lost here
    return jsonify({"count": len(rows), "users": rows})

The client gets {"count": 0, "users": []} with status 200. Nothing looks broken, but no user is ever returned. At minimum, log the exception so the real message reaches your logs:

    except psycopg2.Error as e:
        app.logger.error("list_users query failed: %s", e)
        return jsonify({"error": "database error"}), 500

An empty list and a failed query are different results. Returning a 500 makes the failure visible to monitoring and to the client.

Step 1: look at the real table definition

Open psql and describe the table:

psql -h localhost -U postgres -d app -c "\d users"

     Column   |  Type   | Nullable
--------------+---------+----------
 id           | integer | not null
 name         | text    | not null
 email        | text    | not null
 role         | text    |
 department   | text    |
 is_active    | boolean | not null

You can get the same list with plain SQL, which also works from code:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

Step 2: update every query that uses the old name

Search the code for the old column name. A renamed column is rarely used in only one place - here it appears in both the filtered and the unfiltered query:

grep -rn "active = true" app.py

if role:
    cur.execute(
        "SELECT id, name, email, role, department FROM users "
        "WHERE is_active = true AND role = %s ORDER BY name",
        (role,),
    )
else:
    cur.execute(
        "SELECT id, name, email, role, department FROM users "
        "WHERE is_active = true ORDER BY name"
    )

Be careful with a plain search for the word active: it also matches is_active, inactive and strings in comments. Search for the full expression, or review each match.

Step 3: confirm with real requests

curl -s localhost:8000/users | python3 -c "import sys,json; print(json.load(sys.stdin)['count'])"
curl -s "localhost:8000/users?role=engineer" | python3 -c "import sys,json; print(json.load(sys.stdin)['count'])"

The first number should match the count of active rows in the table:

SELECT count(*) FROM users WHERE is_active = true;

How to stop this from happening again

• Change code and schema together. Put the migration and the query change in the same pull request so they are reviewed and deployed together.
• Rename in steps on a live system. If old and new code run at the same time during a deploy, add the new column first, write to both, move the reads, then drop the old column in a later release.
• Test against a real database. A test that runs the query against a migrated database fails at once on a wrong column name. A test that mocks the cursor does not.
• Never swallow database errors silently. Log them, and return an error status instead of an empty result.