Read/write Postgres large objects with DBI & RPostgres

Question

I'm trying to use the large object (https://www.postgresql.org/docs/10/largeobjects.html) feature of PostgreSQL in R, and I have some trouble writing and reading using {DBI}/{RPostgres}.

Here is what I have tried so far:

# Getting the db
docker run --rm --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5433:5432 postgres

library(DBI)
con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "postgres",
  host = "localhost",
  port = 5433,
  user = "postgres",
  password = "mysecretpassword"
)

Creation works :

> dbGetQuery(con, "SELECT lo_create(1234);")
  lo_create
1      1234

But then I have a hard time figuring out how to write an R object to this large object. For example, how would I write mtcars as a large object in Postgres using {DBI} and {RPostgres}?

And then, how do I read it back again in R?

Answer 1

Consider R's serialize() (the underlying build of .RData/.RDS formats) to save R objects into a Postgres OID column for large objects and use Postgres v10+ server-side large object functions to create and retrieve content. Below can possibly work with bytea types by removing all lo_* functions.

Assuming table structure:

CREATE TABLE my_table(
   ...
   r_object OID
   ...
)

To append the R object:

# PREPARED STATEMENT
sql <- "INSERT INTO my_table(r_object) VALUES (lo_from_bytea(0, ?r_obj))"

# BIND PARAMETER OF SERIALIZED RAW VECTOR
query <- DBI::sqlInterpolate(conn, sql, r_obj = serialize(mtcars))

# EXECUTE ACTION
dbExecute(conn, query)

To retrieve the R object:

sql <- "SELECT lo_get(r_object) AS r_data FROM my_table"
pg_data <- dbGetQuery(conn, sql)

# UNSERIALIZE RETURNED RAW VECTOR
mtcars_from_pg <- unserialize(pg_data$r_data[1])

---

Alternatively, you can use Postgres' TEXT type with unlimited length and R's dput (ASCII representation of R objects) with eval + parse on returned dput string.

CREATE TABLE my_table(
   ...
   r_text TEXT
   ...
)

# PREPARED STATEMENT
sql <- "INSERT INTO my_table(r_text) VALUES (?r_obj)"

# BIND PARAMETER OF DPUT STRING OUTPUT
query <- DBI::sqlInterpolate(conn, sql, r_obj = capture.output(dput(mtcars)))

# EXECUTE ACTION
dbExecute(conn, query)

# RETRIEVE CONTENT
sql <- "SELECT r_text FROM my_table"
pg_data <- dbGetQuery(conn, sql)

# UNSERIALIZE RETURNED RAW VECTOR
mtcars_from_pg <- eval(parse(text=pg_data$r_text[1]))