将postgres数组逗号分隔值转换为行和列

SELECT unnest(your_array_column) AS column_name
FROM your_table;

SELECT array_agg(column_name) AS new_array
FROM (
    SELECT unnest(your_array_column) AS column_name
    FROM your_table
) AS subquery;

-- 创建示例表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    products TEXT[]
);

-- 插入示例数据
INSERT INTO orders (products)
VALUES (ARRAY['1,2,3']), (ARRAY['4,5']), (ARRAY['6,7,8,9']);

-- 将分隔值转换为行和列
SELECT unnest(products) AS product_id
FROM orders;

-- 将分隔值转换为列
SELECT array_agg(product_id) AS product_ids
FROM (
    SELECT unnest(products) AS product_id
    FROM orders
) AS subquery;