Is it possible to run insert queries against PostgreSQL, using dplyr as a database interface ?
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:
https://dbplyr.tidyverse.org/reference/copy_to.src_sql.html
And you can generate a temporary table:
https://dbplyr.tidyverse.org/reference/memdb_frame.html
I don't think you can insert into tables using dbplyr, but you can use SQL code to do so:
https://dbplyr.tidyverse.org/reference/build_sql.html
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 DBI::dbSendQuery()
:
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 dplyr
/dbplyr
syntax. My last link would have required DBI::dbSendQuery()
anyway.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.