如何向PostgreSQL表的自定义类型数组列插入数据？

先看你创建的自定义类型与数据表：

CREATE TYPE mercadoria AS (
    codigo int,
    preco decimal,
    icms decimal
);

CREATE TYPE item_pedido AS (
    numeroItem int,
    quantidade decimal,
    desconto decimal,
    mercadoria mercadoria
);

CREATE TABLE pedido (
    codCliente int references cliente(codigo),
    codigo int primary key,
    data_pedido Date,
    data_entrega Date,
    itemPedido item_pedido[]
);

你尝试的插入代码无法运行，问题出在数组构造逻辑错误，嵌套类型也未明确指定，导致数据库无法正确解析。以下是两种可行的插入方法：

方法一：用ARRAY构造器+显式类型转换 #

这是最清晰的写法，明确指定每个嵌套类型的类型：

INSERT INTO pedido VALUES (
    32,
    1,
    '2023-01-01',
    '2023-01-02',
    ARRAY[
        ROW(1, 2.5, 0.2, ROW(2, 33.3, 5.67)::mercadoria)::item_pedido,
        ROW(1, 2.5, 0.2, ROW(6, 7.3, 0.7)::mercadoria)::item_pedido,
        ROW(1, 2.5, 0.2, ROW(9, 10.6, 15.89)::mercadoria)::item_pedido
    ]
);

方法二：用字符串格式的数组表示 #

如果习惯字符串写法，可以直接用带类型转换的字符串数组：

INSERT INTO pedido VALUES (
    32,
    1,
    '2023-01-01',
    '2023-01-02',
    '{
        "(1,2.5,0.2,(2,33.3,5.67))",
        "(1,2.5,0.2,(6,7.3,0.7))",
        "(1,2.5,0.2,(9,10.6,15.89))"
    }'::item_pedido[]
);

错误原因说明 #

• 原代码给数组外层多套了ROW()，PostgreSQL的数组不需要用ROW包裹，直接用ARRAY[]或者字符串数组即可。
• 嵌套自定义类型时，显式添加::mercadoria和::item_pedido类型转换，能避免数据库因类型推导模糊而报错。

内容的提问来源于stack exchange，提问作者Matheus Henriques