PostgreSQL: column does not exists-CodePudding

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?

CodePudding user response：

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.