PostgreSQL - INSERT an array of composite type containing arrays PostgreSQL - INSERT an array of composite type containing arrays postgresql postgresql

PostgreSQL - INSERT an array of composite type containing arrays


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.