如何将含interval类型列的本地tibble写入PostgreSQL数据库？

报错原因 #

本地tibble的my_interval列是字符（text）类型，而数据库表的对应列是interval类型。执行rows_upsert时，生成的SQL会尝试直接比较text和interval类型，但PostgreSQL没有默认的匹配运算符，因此抛出类型不兼容的错误。

解决方案 #

方法1：显式指定复制时的列类型 #

将本地数据复制到数据库临时表时，明确指定my_interval为interval类型，确保临时表与目标表的列类型一致：

library(dbplyr)
library(dplyr)

con <- my_db_con

df1 <- tibble(my_interval = "P3D")

# 复制本地数据到数据库临时表，指定列类型为interval
df1_db <- copy_to(
  con, 
  df1, 
  temporary = TRUE, 
  col_types = list(my_interval = "interval")
)

df2 <- tbl(con, sql("select 'P3D'::interval as my_interval"))

# 使用数据库端的临时表执行upsert
rows_upsert(df2, df1_db, by = "my_interval")

方法2：使用lubridate类型转换（自动映射） #

借助lubridate包将本地字符转换为R的interval/duration类型，dbplyr会自动将其映射为PostgreSQL的interval类型：

library(dbplyr)
library(dplyr)
library(lubridate)

con <- my_db_con

# 将字符转换为lubridate interval类型
df1 <- tibble(my_interval = interval(3, "days"))
# 或者用 duration("3 days")，两者均可被dbplyr识别为PostgreSQL interval

df2 <- tbl(con, sql("select 'P3D'::interval as my_interval"))

rows_upsert(df2, df1, copy = TRUE)

方法3：在SQL层面显式转换（适用于复杂场景） #

如果需要直接在upsert的SQL逻辑中处理类型转换，可以修改匹配条件，显式将字符列转为interval：

library(dbplyr)
library(dplyr)

con <- my_db_con

df1 <- tibble(my_interval = "P3D")
df2 <- tbl(con, sql("select 'P3D'::interval as my_interval"))

# 自定义upsert逻辑，显式转换RHS的text为interval
rows_upsert(
  df2, 
  df1, 
  copy = TRUE,
  on_conflict = sql(
    "ON CONFLICT (my_interval) DO UPDATE SET 
     my_interval = EXCLUDED.my_interval::interval"
  ),
  by = "my_interval"
)

注意：方法3需要确保你的PostgreSQL表有基于my_interval的唯一约束，ON CONFLICT子句才能生效。

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