Upsert fails when a date column contains NAs, (generated sql produces NULL:text)

[vspinu]

I started seeing errors in my upsert jobs after an update. I couldn't track the working version of neither dbx or Rpostgres, so it could be that some other dep changed something (or it could be on my side, though unlikely as nothing changed in a year except of the upgrade).

When a date column contains NAs the sql generated in dbxUpsert looks like this:

INSERT INTO xyz ("id", "date")
VALUES (7678215432::float8, NULL::text),
       (7678215637::float8, NULL::text),
       (7678215935::float8, '2022-01-31 23:00:00.000000 UTC'),
       (7678216188::float8, '2022-01-31 23:00:00.000000 UTC'),
...
ON CONFLICT ("id")
DO UPDATE SET "date" = excluded."date"

I get the following error from Postgres

ERROR:  column "date" is of type timestamp with time zone but expression is of type text
LINE 1: ... "date") VALUES (7678215432::float8, NULL::text...
                                                ^
HINT:  You will need to rewrite or cast the expression.

Any ideas would be super appreciated as I have many tables pushed with dbxUpsert into datawarehouse with loads of dependents which currently fail :fearful: Thanks!

Postgress version: PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1)
dbx versions tried: 0.2.6, 0.2.8
Rposgres versions tried: 1.3.1, 1.4.3
R: 4.1.2

[ankane]

Hey @vspinu, try downgrading RPostgres to 1.4.1. There was a breaking change introduced in 1.4.2. https://github.com/r-dbi/RPostgres/issues/357

[vspinu]

Wow, your reaction time is amazing! That worked. I must have done something stupid on my side when I was downgrading RPostgres earlier.

Thanks a bunch!!! You saved my day :grinning:

[krlmlr]

It works for date and timestamp columns, looks like the data has strings:

library(RPostgres)

con <- postgresDefault()
dbQuoteLiteral(con, dttr2::NA_Date_)
#> <SQL> NULL::date
dbQuoteLiteral(con, dttr2::NA_POSIXct_)
#> <SQL> NULL::timestamp

^{Created on 2022-02-04 by the reprex package (v2.0.1)}

Not sure what the right way is here. I'd like to keep the typed literals.