RPostgreSQL and DBI: "operator does not exist: uuid = text"

Question

When using dbReadTable to read in database tables that uses UUID as the primary key, I get the following warning message.

1: In postgresqlExecStatement(conn, statement, ...) : RS-DBI driver warning: (unrecognized PostgreSQL field type uuid (id:2950) in column 0)

When I modify the table I loaded and try to update the database using, I get the following error message:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: operator does not exist: uuid = text

I get that the UUID type is not available in R, but is there a way that we can make the database believe the character vector "unique_id" is UUID instead of text?

Code:

library(RPostgreSQL)
library(postGIStools)
pgdrv <- dbDriver(drvName = "PostgreSQL")

# === open connection
db <- DBI::dbConnect(pgdrv,
                     dbname="database",
                     host="localhost", port=5432,
                     user = 'postgres')

# === get tables
users <- dbReadTable(db, "app_users")

# === interaction with tables
users$employee_has_quit[1:5] <- TRUE

# === update tables
postgis_update(conn = db,
               df = users,
               tbl = "app_users",
               id_cols = "unique_id",
               update_cols = "employee_has_quit")

# === close conncetion
DBI::dbDisconnect(db)

Where is the error, in `dbReadTable` if so why are you pasting everything else below it? or are you getting the error in `postgis_update`? — Evan Carroll, Oct 01 '18 at 05:10

Evan Carroll · Accepted Answer · 2018-10-01T05:24:45.460

The problem is a bug in postGIStools. You can see the code they're using to generate this error here

query_text <- paste(query_text, ") AS", tbl_tmp, "(",
                    paste(quote_id(colnames(df)), collapse = ", "), ")",
                    "WHERE", paste(paste0(tbl_q, ".", id_q), "=",
                                   paste0(tbl_tmp, ".", id_q),
                                   collapse = " AND "))

Simply put, that won't work. They should be suing placeholders. It assumes that the input type can be the result of make_str_quote (by proxy of df_q and quote_str). That's a faulty assumption as seen here,

CREATE TABLE foo ( a uuid );
INSERT INTO foo VALUES ( quote_literal(gen_random_uuid()) ) ;

ERROR:  column "a" is of type uuid but expression is of type text
LINE 1: INSERT INTO foo VALUES ( quote_literal(gen_random_uuid()) ) ...
                                 ^
HINT:  You will need to rewrite or cast the expression.

My suggestion is you follow the docs,

Note: This package is deprecated. For new projects, we recommend using the sf package to interface with geodatabases.

You may be able to work around this by doing this

CREATE CAST (varchar AS uuid)
  WITH INOUT
  AS IMPLICIT;

Or, if you are using postgis with rasters, then the `rpostgis` package, works really well and is in active development ([https://github.com/mablab/rpostgis](https://github.com/mablab/rpostgis)). — MalditoBarbudo, Dec 10 '19 at 22:05

RPostgreSQL and DBI: "operator does not exist: uuid = text"

1 Answers1