在 postgres 中将列数据类型从 VARCHAR 转换为 ARRAY（使用 Psycopg2）

Question

我有一个熊猫数据框 df，我想将其作为模式存储在数据库中。默认情况下，它将文本作为默认数据类型。我使用 dtype=sqlalchemy.types.VARCHAR 参数将列保存为 VARCHAR。df.to_sql('data', con, schema='schema', index= False, dtype=sqlalchemy.types.VARCHAR)但是，我的专栏之一的数据类型是多维“数组”。我尝试了以下代码，但没有得到预期的输出。cursor.execute("alter table data alter col2 drop default;alter table data alter col2 type text[][] using array[col2];alter table data alter col2 set default '{}'");上面的代码将所需的列转换为文本数组，但数组为空。我的数据如下所示：col1     col2A1       A1:DEF, Human; X2:XYZ, Mouse;  Y1:RST, RatB1       B1:GHI, Human; Y2:ZXY, MouseC1       C1:JKL, Human; Z2:USC, Mouse我想将 col1 存储为 VARCHAR，将 col2 存储为大小为 n 的多维数组，存储每个部分以 ';' 分隔 作为一个元素。For A1:array[1]: A1:DEF, Humanarray[2]: X2:XYZ, Mousearray[3]: Y1:RST, Rat任何建议，如何使它工作？

Answer 1

使用功能string_to_array()：

alter table data alter col2 drop default;

alter table data alter col2 type text[] using string_to_array(col2, '; ');

alter table data alter col2 set default '{}';

如果你真的想获得多维数组，接下来更新表：

update data d

set col2 = s.col2

from (

    select col1, array_agg(string_to_array(elem, ', ')) as col2

    from data

    cross join unnest(col2) elem

    group by col1) s

where d.col1 = s.col1;

Db<>小提琴。