R语言dbWriteTable函数报错：找不到PostgreSQLConnection继承方法

问题描述 #

执行R代码时，最后一行dbWriteTable函数触发错误，已尝试将数据转换为data.frame，但问题仍未解决。

错误信息 #

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"PostgreSQLConnection", "character", "missing"’

完整代码 #

#############Import required libraries#############
library(tidyverse)
library(lubridate)
library(stringr)
library(openxlsx)
library(RPostgreSQL)


#################### Clear environment & set Working Directory #################
#rm(list=ls())

setwd(dirname(rstudioapi::getActiveDocumentContext()$path))

data <- read.csv("Input Files/new_customer_3_24.csv")

# Convert site# and customer# columns to numeric
data$site.ID <- sprintf("%03d", data$site.ID)
data$customer.ID <- sprintf("%06d", data$customer.ID)

# Create co_cust_no by concatenating values from two columns
co_cust_no <- paste0(format(data$site.ID, format = "000"), "-", format(data$customer.ID, format = "000000"))

# Add the new column to the data 
data <- cbind(data, co_cust_no)

# Subset the data frame to keep only co_cust_no
data <- data[, c("co_cust_no")]
data <- as.data.frame(data)


# Open connection to using credentials in excel file. 
myconn <-dbConnect(dbDriver("PostgreSQL")
                   , read.xlsx("~/sqlp.xlsx", colNames = F)[5,7]
                   , port=read.xlsx("~/sqlp.xlsx", colNames = F)[6,7]
                   , dbname=read.xlsx("~/sqlp.xlsx", colNames = F)[2,7]
                   , user=read.xlsx("~/sqlp.xlsx", colNames = F)[3,7]
                   , password=read.xlsx("~/sqlp.xlsx", colNames = F)[4,7])

#Create a tmp table with co_cust_no
dbSendQuery(myconn, "CREATE TEMPORARY TABLE hisp (co_cust_no varchar(50))")
dbWriteTable(myconn, name = "hisp", values = data, temporary = TRUE, overwrite = TRUE)

错误原因与解决方案 #

错误原因 #

报错核心是dbWriteTable的参数组合不匹配：

1. 先通过dbSendQuery手动创建临时表，随后又在dbWriteTable中指定temporary=TRUE，两个操作冲突导致函数无法识别正确调用签名。
2. 未明确指定row.names=FALSE，默认情况下dbWriteTable会尝试写入行名，可能引发列结构不匹配问题。

修复步骤 #

1. 移除手动创建临时表的语句：dbWriteTable的temporary=TRUE参数会自动创建临时表，无需提前手动创建。
2. 添加row.names=FALSE参数：避免将R数据框的行名写入数据库表，确保列结构匹配。

修改后的代码片段：

# 直接用dbWriteTable创建临时表并写入数据
dbWriteTable(myconn, name = "hisp", values = data, temporary = TRUE, overwrite = TRUE, row.names = FALSE)

额外检查 #

• 执行str(data)确认data是结构正确的data.frame，且列名co_cust_no与目标表列名一致。
• 确保RPostgreSQL包是最新版本，可通过update.packages("RPostgreSQL")更新。

内容的提问来源于stack exchange，提问作者Shobi