Dates escaped incorrectly in Postgres

[carlganz]

Possibly similar to #661 but originally posted here: r-dbi/RPostgres#362

library(RPostgres)
# use whatever Postgres connection you have
con <- postgresDefault()
dbplyr::sql_escape_date(con, Sys.Date())
# <SQL> '2021-12-02'
dbQuoteLiteral(con, Sys.Date())
# <SQL> '2021-12-02'::date

[hadley]

What should it be?

[carlganz]

The second one with the explicit date casting is correct. @krlmlr can confirm

[mgirlich]

The same is true for other databases and also for datetimes. Maybe we should simply use dbQuoteLiteral() for real databases instead of using custom escape functions?

[hadley]

@mgirlich yeah, if dbQuoteLiteral() is implemented we should use it, but I'm not sure how widespread support is.

[hadley]

In general we want to push these sorts of fixes upstream as much as possible, but it's fiddly to make sure everything keeps working for folks regardless of the combination of dbplyr and upstream package installed.

[hadley]

I think the easiest fix here would be to supply sql_escape_date and sql_escape_datetime methods for Postgres that just called dbQuoteLiteral().