Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to write R object to Postgres large object #376

Open
ColinFay opened this issue Dec 9, 2021 · 4 comments · May be fixed by #472
Open

How to write R object to Postgres large object #376

ColinFay opened this issue Dec 9, 2021 · 4 comments · May be fixed by #472

Comments

@ColinFay
Copy link

ColinFay commented Dec 9, 2021

Cross-posted with https://stackoverflow.com/questions/70271590/read-write-postgres-large-objects-with-dbi-rpostgres

I'm trying to use the large object feature from Postgres https://www.postgresql.org/docs/10/largeobjects.html but I have a hard time wrapping my head around converting R object and writing them to the db.

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?

@krlmlr
Copy link
Member

krlmlr commented Dec 20, 2021

Thanks. Can you use lo_from_bytea(), lo_put() and lo_get(), perhaps with dbQuoteLiteral() to quote a blob value?

@jjesusfilho
Copy link

jjesusfilho commented Jan 16, 2023

I use lo_import() to insert images as blobs in PostgreSQL:

DBI::dbExecute(conn, "create table my_table( id int, image oid)")

DBI::dbGetQuery(conn, "insert into my_table (id, image)
                                   values(1, lo_import('absolute_path_to_image_file')) returning image")
      image
1      1234

In order to bring the image to R, I use lo_get():

df <-  DBI::dbGetQuery(conn, "select lo_get(1234) as image")

But those functions only work on the server side, i.e., the file must be on the same server as PostgreSQL. Also, the user must be a superuser. It would be great if someone built an R package to work on the client side: client interfaces.

@krlmlr
Copy link
Member

krlmlr commented Apr 1, 2024

There is a libpq function lo_import() that accepts files on the client system. Does the R interface need to do more than calling this function?

@toppyy
Copy link

toppyy commented Oct 19, 2024

Added a PR that introduces the postgresImportLargeObject() for importing large objects from client side. I used libpq's lo_import_with_oid() so you can import to a specific oid if need be.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants