R 无法通过 rPostgreSQL 将表写入 PostgreSQL

Question

我是 PostgreSQL 的新手，我正在尝试使用 R 将表写入 PostgreSQL。请参阅下面的 R 代码；首先制作一些可重现的数据框:

> Time<-c('201512', '201511', '201510')
> Department<-c('ABC', 'BCA', 'NBA')
> Pro_Type<-c('standard', 'maintain', 'sustaining')
> Man_month<-c('111.4', '124.1', '232.1')
> ID<-c('1','2', '3')
> melt_short<-data.frame(Time, Department, Pro_Type, Man_month, ID)
> melt_short
   Time  Department   Pro_Type Man_month ID
1 201512        ABC   standard     111.4  1
2 201511        BCA   maintain     124.1  2
3 201510        NBA sustaining     232.1  3

然后创建连接:

 require("RPostgreSQL")

# loads the PostgreSQL driver
  drv <- dbDriver("PostgreSQL")
# creates a connection to the postgres database
  con <- dbConnect(drv, dbname = "XXX",
             host = "XXX.XX.XX.XXX", port = 5432,
             user = "postgres", password = XXXXXX)

然后我在 PostgreSQL 数据库中创建一个表:

 CREATE TABLE dms_melt
(
  "Time" numeric,
  "Department" character(1),
  "Pro_type" character(1),
  "Man_month" numeric,
  "ID" numeric NOT NULL,
  CONSTRAINT dms_melt_pkey PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
 ALTER TABLE dms_melt
 OWNER TO postgres;

最后执行写表代码，报错:

dbWriteTable(con, "dms_melt", value = melt_short, append = T,row.names=F)

Error in postgresqlgetResult(new.con) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  value too long for   
type character(1) CONTEXT:  COPY dms_melt, line 1, column Department: "ABC"

应该没有那么难，但我一直在寻找解决方案，但没有成功。实际上我已经使用“mtcars”数据集来写表并且没有问题。

data(mtcars)
df <- data.frame(carname = rownames(mtcars), 
                 mtcars, 
                 row.names = NULL)
df$carname <- as.character(df$carname)

dbWriteTable(con, "cartable", 
             value = df, append = TRUE, row.names = FALSE)
[1] TRUE

插入我创建的 PostgreSQL:

CREATE TABLE cartable
(
  carname character varying,
  mpg numeric(3,1),
  cyl numeric(1,0),
  disp numeric(4,1),
  hp numeric(3,0),
  drat numeric(3,2),
  wt numeric(4,3),
  qsec numeric(4,2),
  vs numeric(1,0),
  am numeric(1,0),
  gear numeric(1,0),
  carb numeric(1,0)
 )
 WITH (
  OIDS=FALSE
 );
 ALTER TABLE cartable
 OWNER TO postgres;

提前感谢您的任何建议和想法。

Answer 1

注意下面两个表格列的数据类型:

...
"Department" character(1),
"Pro_type" character(1),
...

现在让我们看看您要插入的数据:

   Time  Department   Pro_Type Man_month ID
1 201512        ABC   standard     111.4  1
2 201511        BCA   maintain     124.1  2
3 201510        NBA sustaining     232.1  3

现在是错误信息:

Error in postgresqlgetResult(new.con) :
RS-DBI driver: (could not Retrieve the result : ERROR:  value too long for
type character(1) CONTEXT:  COPY dms_melt, line 1, column Department: "ABC"

PostgreSQL 中的character(N) 数据类型是定长字符串类型。该列中的每个值都必须是长度为 N 的字符串。对于 character(1)，这意味着每个值都必须包含一个字符。

值 'ABC' 是一个长度为 3 的字符串。它对于 character(1) 数据类型来说太长了。

您可能会将 R 字符向量类型与 SQL 字符类型混淆。在 R 中，字符向量的每个元素都是一个可变长度的字符串。具有讽刺意味的是，您可以使用语法 character(N) 在 R 中构造一个字符向量，其中 N 是结果向量中可变长度字符串元素的数量。在 SQL 中，您可以将列视为向量，但数据类型始终指的是单个单元格值的标量类型。 SQL语法中的Ncharacter(N)是指每个字符串中的字符个数。

你的 mtcars 测试成功的原因是因为该表中唯一的字符串列是 carname 其数据类型 character varying 没有长度规范。来自PostgreSQL documentation :

If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

因此，您可以通过对这两列使用character varying(或者更简洁的名称varchar)来解决问题。您可以像在 mtcars 架构中一样省略长度规范，或者如果您知道这些列的值的契约(Contract)长度限制，您可以指定 varchar(N)其中 N 是长度限制。此外，如果 Department 值始终为 3 个字符长，则您可以选择使用 char(3) 作为固定长度类型。

总结:

SQL
==========

character(N)            fixed-length strings of length N
char(N)                 alias for character(N)
character               alias for character(1)
char                    alias for character(1)
character varying(N)    variable-length strings of maximum length N
varchar(N)              alias for character varying(N)
character varying       variable-length strings of unlimited length
varchar                 alias for character varying

R
==========

character(N)            vector of length N of variable-length strings