如何在duckdb R中修改数据约束

jum4pzuy 于 2023-05-20 发布在其他

关注(0)|答案(1)|浏览(141)

我试图在duckdb（R API）中将Not Null约束更改为Null约束，但无法使其保持不变。下面是一个问题的例子。

drv<- duckdb()
con<- dbConnect(drv)
dbExecute(con, "CREATE TABLE db(a varchar(1) NOT NULL, b varchar(1) NOT NULL)")

d<- data.table(a = 1:3, b=c("a", NA, "b"))

#test 'not null' constraint
dbWriteTable(con, name = "db", value = d, append = T)
Error: Constraint Error: NOT NULL constraint failed: db.b

所以在这里我尝试修改'db'以允许我在列b中写入'd'及其NA值

> dbSendQuery(con, "ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL")
<duckdb_result 46970 connection=b6ff0 statement='ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL'>

> dbWriteTable(con, name = "db", value = d, append = T)
Error: Constraint Error: NOT NULL constraint failed: db.b

看起来“dbSendQuery”改变表工作了（至少没有错误），但很明显约束没有改变。有什么想法可以让约束的改变保持不变吗？

来源：https://stackoverflow.com/questions/76240893/how-to-alter-data-constraint-in-duckdb-r

1条答案

按热度按时间

oaxa6hgo1#

使用DROP NOT NULL代替SET NOT NULL。

d <- data.table(a = 1:3, b=c("a", NA, "b"))DBI::dbWriteTable(duck, name = "db", value = d, append = T)
duck <- DBI::dbConnect(duckdb::duckdb())
DBI::dbWriteTable(duck, name = "db", value = d, append = T)
# ERROR [2023-05-13 14:11:15] {"msg":"uncaught error","error":"rapi_execute: Failed to run query\nError: Constraint Error: NOT NULL constraint failed: db.b","where":["DBI::dbWriteTable(duck, name = \"","DBI::dbWriteTable(duck, name = \"",".local(conn, name, value, ...)"],"pid":"511449"}
# Error: rapi_execute: Failed to run query
# Error: Constraint Error: NOT NULL constraint failed: db.b

固定：

DBI::dbExecute(duck, "ALTER TABLE db ALTER COLUMN b DROP NOT NULL")
# [1] 0
DBI::dbWriteTable(duck, name = "db", value = d, append = T)
DBI::dbGetQuery(duck, "select * from db")
#   a    b
# 1 1    a
# 2 2 <NA>
# 3 3    b

赞(0）回复(0）举报 2023-05-20

我来回答

如何在duckdb R中修改数据约束

1条答案

相关问题

热门标签

最新问答