postgresql 如何在 RPostgreSQL 中通过 dbWriteTable(..., append=TRUE) 使用列默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22153034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use column defaults with dbWriteTable(..., append=TRUE) in RPostgreSQL
提问by Zach
The dbWriteTablefunction in RPostgreSQLseems to ignore column names and tries to push data from R to PostgreSQL as-is. This is problematic when appending to existing tables, particularly if there are columns un-specified in the R object that should be given default values.
的dbWriteTable在功能上RPostgreSQL似乎忽略的列名，并试图自R将数据推到PostgreSQL原样。这在附加到现有表时是有问题的，特别是如果 R 对象中有未指定的列应该被赋予默认值。
RMySQL handles this case very gracefully by adding the column names to LOAD DATA LOCAL INFILE. How do I force RPostgreSQL to assign default values to un-specified columns in dbWriteTablewhen append=TRUE?
RMySQL 通过将列名添加到LOAD DATA LOCAL INFILE. 如何强制 RPostgreSQL 在dbWriteTablewhen 中为未指定的列分配默认值append=TRUE？
Here is an example:
下面是一个例子：
CREATE TABLE test (
column_a varchar(255) not null default 'hello',
column_b integer not null
);
insert into test values (DEFAULT, 1);
Which yields the following table:
产生下表：
select * from test;
column_a | column_b
----------+----------
hello | 1
(1 row)
I want to insert some new data to this table from R:
我想从 R 向这个表插入一些新数据：
require('RPostgreSQL')
driver <- PostgreSQL()
con <- dbConnect(driver, host='localhost', dbname='development')
set.seed(42)
x <- data.frame(column_b=sample(1:100, 10))
dbWriteTable(con, name='test', value=x, append=TRUE, row.names=FALSE)
dbDisconnect(con)
But I get the following error:
但我收到以下错误：
Error in postgresqlgetResult(new.con) :
RS-DBI driver: (could not Retrieve the result : ERROR: missing data for
column "column_b"
CONTEXT: COPY test, line 1: "92"
)
This is because I have not specified the column_afield, so dbWriteTableis trying to write the data for column_b into column_a. I would like to force dbWriteTableto use the defaults for column_a, and properly write column_bto column_b.
这是因为我没有指定column_a字段，所以dbWriteTable试图将 column_b 的数据写入 column_a。我想强制dbWriteTable使用的默认设置column_a，并妥善写column_b来column_b。
I should only get a failure if:
我应该只在以下情况下失败：
- I fail to specify a column with no default value
- I try to insert a column that doesn't exist in the table
- I insert the wrong datatype into an existing column
- 我无法指定没有默认值的列
- 我尝试插入表中不存在的列
- 我将错误的数据类型插入到现有列中
采纳答案by Alex
I had exactly the same problem, this fixed it.
我有完全相同的问题，这解决了它。
Check out the dbWriteTable2function from package caroline.
查看dbWriteTable2package 中的函数caroline。
The code then allows you to write a data frame without an id column into the database using add_id = TRUE, e.g.
然后，该代码允许您使用 将没有 id 列的数据框写入数据库add_id = TRUE，例如
dbWriteTable2(con_psql,"domains",data_domains,append=TRUE,overwrite=FALSE,row.names=FALSE,add.id=TRUE)
