Converting integer to date for PostgreSQL

Question

I'm trying to select specific dates from a PostgreSQL table with the following function:

cur.execute(""" SELECT * FROM posting_log WHERE hol_date = {}""".format(condition))

When I run this, however, I receive the following trace:

psycopg2.ProgrammingError: operator does not exist: date = integer

LINE 1: SELECT * FROM posting_log WHERE hol_date = 2018-10-10

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The function is clearly picking up the date I want to check for, however thinks it's an integer and is unable to convert it to date format. What can I do to solve this?

I've tried the following solutions provided by this answer, however that simply throws an error saying the functions date and to_date do not exist:

SELECT date(condition::TEXT)
SELECT to_date(condition::text, 'YYYY-MM-DD')

The date value must be in single quotes: `SELECT * FROM posting_log WHERE hol_date = '2018-10-10'` — 404, Oct 23 '18 at 16:00
Thats done it, I added ' ' either side of {}. Thanks for the help. — Laurie, Oct 23 '18 at 16:06
Don't use string formatting for parameters- this leads you open to SQL injection attacks. Imagine if some malicious actor put `condition = "0; DROP TABLE posting_log;"`. (imagine it, *don't* try it). Instead use a parameterized query which should be as simple as as: `cur.execute(""" SELECT * FROM posting_log WHERE hol_date = %s""", condition)`. **Edit**: Obligatory [Bobby Drop Tables](https://xkcd.com/327). — pault, Oct 23 '18 at 16:25
@LaurieBamber please don't do what eurotrash suggested. Using parameterised queries as pault suggests is the right thing to do unless you have very specific requirements and you've validated the contents of `condition` so you don't suffer from injection attacks — Sam Mason, Oct 23 '18 at 16:37

score 2 · Accepted Answer · answered Oct 23 '18 at 17:47

I cannot stress this enough, do not use naive template variables, always "mogrify" you arguments. This means letting a well tested software handle inserting of variables into your SQL queries. This is to prevent SQL injection. Better to just do it always, so that you don't forget when it is critical.

Anyway, the issue you are seeing is that your manual way of doing it doesn't include single quotes around the date. The query which is being run is:

SELECT * FROM posting_log WHERE hol_date = 2018-10-10

... while the one you want is:

SELECT * FROM posting_log WHERE hol_date = '2018-10-10'

One way to do this quickly and safely is as follows:

args = ['2018-10-10']
cur.execute("""SELECT * FROM posting_log WHERE hol_date = %s""", args)

This should add the necessary single quotes, and will automatically "mogrify" your arguments.

Converting integer to date for PostgreSQL

1 Answers1