PostgreSQL-插入包含数组的复合类型数组 [英] PostgreSQL - INSERT an array of composite type containing arrays

问题描述

我有一个包含TEXT等数组的复合类型。我在主表中使用它来创建复合类型的数组。

如何生成INSERT命令（不使用默认值）复合类型的字段名称）我可以用复合数组创建一个临时表，然后将其插入主表吗？

I have a composite type containing arrays of TEXT, etc. I am using this inside my main table to create an array of composite type.
How do I generate an INSERT command (without using the default field names of the composite type) ? Can I create a TEMPORARY TABLE with the array of composites and then insert it into the main table?

例如：

DROP TABLE collection;
DROP TABLE book_set;
DROP TYPE book;

CREATE TYPE book AS ( title TEXT, authors TEXT[], extra_spare TEXT );
CREATE TEMPORARY TABLE book_set ( books book[] );
CREATE TABLE shelf_collection ( shelf INT, position INT, books book[] );

-- Prefer to specify the fields I want, and NOT extra_spare as shown here!
-- AND it doesn't yet work... needs more casting?
INSERT INTO book_set( books ) VALUES (
      ( 'book1', array[ ( 'author1', 'author2' ) ], '' ),
      ( 'book2', array[ ( 'author3' )            ], '' ) ); 

-- And this obviously does not work yet!
INSERT INTO shelf_collection( shelf, position, books ) VALUES ( 1, 2, book_set ); 

第一次插入失败，并显示以下消息：

The first INSERT fails with the message:

错误：插入的表达式多于目标列。

ERROR: INSERT has more expressions than target columns.

使用或不使用array []失败。

Fails same with or without array[] construct.

我的实际使用情况要复杂得多，因为该复合材料包含其他复合材料以及许多字段。

My real-world usage is significantly more complex, with the composite containing other composites, and many many fields.

出于性能方面的考虑，我没有在此处使用多个表（无需检索即可连接），并且内部组合和数组从未独立引用。

I am not using multiple tables here for performance reasons (no join required to retrieve), and the inner composites and arrays are never referenced independently.

我是使用 perl（5.14.2）和 DBI（1.616）和 psql（9.1.7 ）。

更多信息：

以下方法有效，但是如何更改它，以至于无需指定书的所有字段：

The following works, but how do I change it so that I do not need to specify ALL fields of book:

DROP TABLE shelf_collection;
DROP TYPE book;

CREATE TYPE  book AS          ( title TEXT, authors TEXT[], extra_spare TEXT );
CREATE TABLE shelf_collection ( shelf INT, position INT, books book[] );

INSERT INTO shelf_collection VALUES ( 12, 23, array[ROW( 'book title 1', array[ 'author1', 'author2' ], '' )::book] );

SELECT * FROM shelf_collection;

推荐答案

PostgreSQL数组是有用的抽象（非标准，我应该添加），它很容易被滥用-我认为这正是您要尝试的方法。

PostgreSQL arrays are useful abstraction (non-standard, I should add), bit it can be easily abused - and I think this is exactly what you are trying to do.

您正在尝试使用数组作为借口和捷径不规范您的数据库架构。

You are trying to use arrays as an excuse and shortcut to NOT normalize your database schema. It may work with some kludges, but this is not worth it in the long run.

如果继续使用数组，将无法利用许多优点。真正使SQL有用的结构。例如，您不能有效地在 book_set 表中搜索任何给定的作者。

If you continue to use arrays, you will not be able to take advantage of many constructs which really make SQL useful. For example, you cannot effectively search your book_set table for any given author.

正确的设计是对- book_set 不应包含作者数组。而是创建单独的表 authors 和单独的链接表 book_author 。

Right design would be to normalize - book_set should not contain array of authors. Instead, create separate table authors and separate link table book_author.

当然，采用规范化的方法插入数据更麻烦，查询数据也更麻烦-您将需要执行联接。

Granted, with normalized approach it is more awkward to insert data, and somewhat more awkward to query it - you will need to perform joins.

但是，这使得可以创建几乎所有可以想象的查询。而且，有了正确的索引编制，即使您的数据集非常大，它也可以非常快速地工作。

But, it makes possible to create almost any query imaginable. Also, with proper indexing it makes it work very fast even it your data set is extremely large.

这篇关于PostgreSQL-插入包含数组的复合类型数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！