PostgreSQL: column does not exists

Question

Right now I'm trying to create a filter that would give me every result from start of the month. The query looks like this:

cur.execute('SELECT SUM(money_amount) '
            f'FROM expense WHERE created >= "{first_day_of_month}"'

But I'm getting such error: psycopg2.errors.UndefinedColumn: column "2022-08-01" does not exist

my createtable.sql:

CREATE TABLE budget(
    codename varchar(255) PRIMARY KEY,
    daily_expense INTEGER );

CREATE TABLE category(
    codename VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255),
    is_basic_expense BOOLEAN,
    aliases TEXT );

CREATE TABLE expense(
    id SERIAL PRIMARY KEY,
    money_amount INTEGER,
    created DATE,
    category_codename VARCHAR(255),
    raw_text TEXT,
    FOREIGN KEY(category_codename) REFERENCES category(codename) );

What is wrong and why the column does not exist, when it is?

Use instead: cur.execute("select sum(money_amt) from expense where created >= %s", [first_day_of_month]) — Mike Organek
– Mike Organek, Commented Aug 30, 2022 at 15:28

histocrat · Accepted Answer · 2022-08-30 16:25:24Z

7

This is probably the most common reason to get a "column does not exist" error: using double quotes. In PostgreSQL, double quotes aren't used for strings, but rather for identifiers. For example, if your column name had a space in it, you wouldn't be able to write WHERE the date > '2022-08-01', but you would be able to write WHERE "the date" > '2022-08-01'. Using double quotes around a string or stringy thing like a date gets interpreted as an attempt to use an identifier, and since you're using it where a value should be it will usually be interpreted as trying to identify a column in particular. I make this mistake at least once a week. Instead, use single quotes or placeholders.

answered Aug 30, 2022 at 16:25

histocrat

2,41114 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

That's not Postgres specific. This is how strings and identifiers are defined in the SQL standard

histocrat Over a year ago

It's not unique to Postgres for sure (when I make this mistake it's usually with CockroachDB) but there are a fair number of SQL databases that either treat double quotes as strings or allow them to be strings. mySQL for example. I didn't know Postgres's way was the standard!

etozhefifan Over a year ago

@histocrat thanks for an exhaustive answer. I can't vote up, so just marked the answer as accepted

Collectives™ on Stack Overflow

PostgreSQL: column does not exists

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related