r - Postgres ARRAY 列类型到 R 中的表列表列，反之亦然

Question

假设我正在使用 starwars来自 dplyr 包的数据集，其中包含列表列(用于电影、车辆......)。为了简化，
让我们只使用名称和电影数据:

library(dplyr)
ex_data <- starwars %>%
  select(name, films)

ex_data

# A tibble: 87 x 2
   name               films    
   <chr>              <list>   
 1 Luke Skywalker     <chr [5]>
 2 C-3PO              <chr [6]>
 3 R2-D2              <chr [7]>
 4 Darth Vader        <chr [4]>
 5 Leia Organa        <chr [5]>
 6 Owen Lars          <chr [3]>
 7 Beru Whitesun lars <chr [3]>
 8 R5-D4              <chr [1]>
 9 Biggs Darklighter  <chr [1]>
10 Obi-Wan Kenobi     <chr [6]>
# ... with 77 more rows

我想将此数据复制到 PostgreSQL 数据库，但我希望将电影列转换为 SQL 中的文本数组类型，如下所示:

starwars=# \d test
       Table "public.test"
   Column   |  Type  | Modifiers
------------+--------+-----------
 name       | text   | not null
 films      | text[] |

有没有什么简单的方法可以用 dplyr 做到这一点？和/或 dbplyr ?
此外，我对相反的操作感兴趣，将带有数组类型列的表从 postgreSQL db 导入 R 并为数组一获取列表类型列。
先感谢您!

Answer 1

下面是一个使用现有 R 数据库包和一些 SQL 代码的穷人的解决方法。我使用 数据表用于整理数据的包(可以很容易地适应 dplyr )。

# packages
require(data.table)
require(RPostgreSQL)

# data preparation
dt = dplyr::starwars
setDT(dt)
dt = dt[ , .SD, .SDcols = c('name', 'films') ]
# list conversion
dt2 = dt[ , .(films = unlist(films)), name ]

# database I/O
drv = dbDriver('PostgreSQL')
con = dbConnect(drv,
                dbname = dbname,
                user = user, password = password,
                host = host, port = port)
# write
dbWriteTable(con, name = 'test', value = dt2,
             overwrite = TRUE, row.names = FALSE)
# convert to array in database
q = "DROP TABLE IF EXISTS test2;
     CREATE TABLE test2 AS (
       SELECT name, array_agg(films) AS films
       FROM test
       GROUP BY name
     );"
dbSendQuery(con, q)

# read arrays
dt3 = dbReadTable(con, 'test')

# convert back to list columns
setDT(dt3)
dt4 = dt3[ , .(films = list(films)), name ]

dbDisconnect(con)
dbUnloadDriver(drv)

一些想法:如果 RPostgreSQL 中存在这样的功能就好了或其他数据库包之一。它肯定不能很好地扩展。不幸的是，我缺乏 C 方面的技能来帮助实现这一点。