Is it possible to run insert queries against PostgreSQL, using dplyr as a database interface ?
Check this out for an introduction:
quoting from the tutorial:
"The goal of dbplyr is to automatically generate SQL for you so that you’re not forced to use it. However, SQL is a very large language and dbplyr doesn’t do everything. It focusses on
SELECT statements, the SQL you write most often as an analyst."
Which led me to the thinking that only data retrieval is supported, can anyone confirm that.
You can copy from a data frame to a db:
And you can generate a temporary table:
I don't think you can insert into tables using dbplyr, but you can use SQL code to do so:
You don't really need
dbplyr to run insert queries as
DBI::dbWriteTable() will do.
On second thought (editing my previous answer) you can use dplyr -
dplyr::db_insert_into(). My apologies for confusion about where the
db_insert_into() function lives.
In PostgreSQL context DBI sometimes struggles with schemas, so you might want to consider a little workaround with a temporary table and executing SQL code directly via
dbWriteTable(con, SQL('tmp_tbl'), a_rstats_data_frame, overwrite = T) # a temporary table dbSendQuery(con, 'insert into cruel_and_unusual_schema.final_table select * from tmp_tbl;') #flip the temporary table over dbSendQuery(con, 'drop table tmp_tbl;') #clean up...
Fair point. I was purely basing my answer on using
dbplyr syntax. My last link would have required
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.