PostgreSQL interval invalidate

Question

 select date,
    to_char(shares, '99g999g999g999') as shares,
    to_char(trades, '99g999g999') as trades,
    to_char(dollars, 'L99g999g999g999') as dollars
    from facebook
    where date >= '2017-02-01'  + interval '1 months'
    order by date;

message: ERROR:  invalid input syntax for type interval: "2017-02-01"
LINE 6:  where date >= '2017-02-01'  + interval '1 months

I know there is one way to do it by declare a date variable. so the code would be

 date >= date: 'Variable' + interval '1 month'

But I don't know how to set a date variable in PGadmin.

@a_horse_with_no_name Would you please give me some links about how to declare variables in PGadmin. Thanks. — Mark, Sep 13 '20 at 10:45
SQL (at least standard SQL) has no "variables". You would need to use PL/pgSQL if you want a variable. See [here](https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query) for some workarounds — , Sep 13 '20 at 10:46

score 1 · Answer 1 · answered Sep 13 '20 at 10:44

'2017-02-01' is treated as a string literal, not as a date literal. To make Postgres recognize it as a proper date value, use a proper date literal.

I prefer using ANSI date literals, which are strings prefixed with the keyword date

where date >= date '2017-02-01'  + interval '1 months'

Note that your choice of the column name date makes this quite confusing. The date on the left side refers to the column. The date prefixing the string literal refers to the data type name. To avoid the confusion, don't use reserved words (e.g. data types) as column names.

PostgreSQL interval invalidate

1 Answers1