drob tables their names begin with numbers in postgress using RPostgreSQL

Question

I try to drop tables in an scheme their names begin with digits from R. The tables exist in a postgress data bank.

library(RPostgreSQL)
library(DBI)
pg = dbDriver("PostgreSQL")
con = dbConnect(pg, user= ....)
dbGetQuery(con,paste0("DROP TABLE frc_rm.",'"30062020RB"',";"))

where con containes the login information. However, the command cannot be executed!

In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: DROP TABLE frc_rm."30062020RB";

any idea what could be the solution?

How about: dbExecute(conn, 'DROP TABLE frc_rm."30062020RB"')? Can you run the same command in Postgres using the same user as in R? User may not have create/drop table privileges. — Parfait
– Parfait, Commented Jul 17, 2020 at 22:08

Adrian Klaver · Accepted Answer · 2020-07-17 22:08:48Z

1

Pretty sure this is issue on R side with the function you are using:

https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbGetQuery

" Details

This function is for SELECT queries only. Some backends may support data manipulation statements through this function for compatibility reasons. However callers are strongly advised to use dbExecute for data manipulation statements. "

answered Jul 17, 2020 at 22:08

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

drob tables their names begin with numbers in postgress using RPostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related