PostgreSQL - INSERT an array of composite type containing arrays

Question

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?

For example:

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.

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.

I am using perl(5.14.2) and DBI(1.616) and psql(9.1.7).

MORE INFO:

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;

Welcome to StackOverflow! At this point, it doesn't look like you've gotten to the point of using Perl DBI, so this is really a PostgreSQL question, right? Does the SQL work if you don't use the array types? — Jon Ericson
– Jon Ericson, Commented Jan 8, 2013 at 23:21
Oh. When you say "it doesn't yet work", do you have an error message? Can you copy it and paste it in the question? — Jon Ericson
– Jon Ericson, Commented Jan 8, 2013 at 23:33
I had then entire solution working before I learned about composite types. But doing INSERTs of composites, and especially arrays of compostes containing arrays has me perplexed. And the error message is: ERROR: INSERT has more expressions than target columns — Matt Goldworm
– Matt Goldworm, Commented Jan 9, 2013 at 1:04
Still fails with same error message if I remove the array[] construct. — Matt Goldworm
– Matt Goldworm, Commented Jan 9, 2013 at 3:10
Now have a working example, but still have a question: how do I fix it so I do not need to specify ALL fields of book, since my real-world code has many many fields in the composite. — Matt Goldworm
– Matt Goldworm, Commented Jan 9, 2013 at 5:05

mvp · Accepted Answer · 2013-01-09 06:39:20Z

4

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.

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.

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.

answered Jan 9, 2013 at 6:39

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matt Goldworm Over a year ago

Thanks mvp. I understand your response. However, in my real-world case, I really do NOT want a separate table for performance reasons. Once I do the SELECT from the main table, I need the arrays to be there without having to do JOINS with the extra associated I/O. I understand that the data within the arrays will not be searchable, but that is not an issue. Much of that data has already been copied from other tables, and is there (duplicated) for performance.

mvp Over a year ago

You may not realize it, but if your arrays are bigger than certain threshold, Postgres will have to offload your data into invisible TOAST table (which is split into fixed size chunks and joined with master table). This is same joining, but without you controlling it. Do real joins - they are not necessarily slow, if you do it right

Matt Goldworm Over a year ago

PS: I started with a normalized DB, but wound up with 9 JOINS to get the data for my predominant hi-volume query.

Matt Goldworm Over a year ago

PPS: The arrays are generally only 1 entry... occasionally 2-3

Jon Ericson Over a year ago

@MattGoldworm: To echo mvp, you shouldn't fear joins, not even lots of them. A relational database is designed with joins in mind. If you are having performance issues, you should take a look at tuning your database first and only sometime later (if ever) denormalizing your data.

Collectives™ on Stack Overflow

PostgreSQL - INSERT an array of composite type containing arrays

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related