I'm wondering if there's a way to import PostgreSQL dump through R (RPostgreSQL) into a PostgreSQL database.
I understand this is the way we write csv or a datatable from R into PostgreSQL through RPostgreSQL.
dbWriteTable(con, 'mtcars', mtcars)
This is the way we can import the dump into PostgreSQL database in the terminal.
\i /Users/Downloads/mypostgres.dump
Is there a way I can import the dump into PostgreSQL through a R package, so I don't have to leave the R interface?
You could use system and call psql:
system('psql my_new_db < /Users/Downloads/mypostgres.dump')
system() gives me the hint on how to run PostgreSQL terminal things in R, which solves me a lot of pain. The pg_restore is not working for me in ths case but I found system('psql my_new_db < /Users/Downloads/mypostgres.dump'). If you wish to edit you answer, I'm happy to tick this off.You can try:
library("RPostgreSQL")
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "postgres",
host = "localhost", port = 5432,
user = "openpg", password = "password")
dbWriteTable(con, "cartable", value = mtcars, append = TRUE, row.names = FALSE)
#Test if it is there
head(dbGetQuery(con, "SELECT * from cartable"))
# mpg cyl disp hp drat wt qsec vs am gear carb
#1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
#2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
#3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
#5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
#6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1
