INSERT into table with array of composite type

Question

So I am trying to copy values from temporary tables into a table with an array type and I get the following error which I can't find a way to overcome.

ERROR:  cannot assign to field "addressid" of column "address" because its type addresstype[] is not a composite type
LINE 32: ...nID,Person.Title,Person.FirstName,Person.LastName,Person.Add...
                                                              ^
********** Error **********

ERROR: cannot assign to field "addressid" of column "address" because its type addresstype[] is not a composite type
SQL state: 42804

Here is the query I try:

INSERT INTO Customer (CustomerID,Person.PersonID,Person.Title,Person.FirstName,Person.LastName,Person.Address.AddressID,Person.Address.AddressLine1,Person.Address.AddressLine2,Person.Address.City,Person.Address.PostalCode)
SELECT TCustomer.CustomerID,TPerson.PersonID,TPerson.Title,TPerson.FirstName,TPerson.LastName,TAddress.AddressID,TAddress.AddressLine1,TAddress.AddressLine2,TAddress.City,TAddress.PostalCode
FROM TCustomer,TPerson,TPersonAddress,TAddress
WHERE TCustomer.PersonID = TPerson.PersonID
AND TPerson.PersonID = TPersonAddress.PersonID
AND TPersonAddress.AddressID = TAddress.AddressID;

tables&types:

CREATE TYPE AddressType AS(
AddressID integer,
AddressLine1 text,
AddressLine2 text,
City text,
PostalCode text);

CREATE TYPE PersonType AS(
PersonID integer, 
Title text,
FirstName text,
LastName text,
Address AddressType[]);

CREATE TABLE Customer(
CustomerID integer,
Person PersonType,
PRIMARY KEY(CustomerID));

CREATE TEMPORARY TABLE TAddress (AddressID integer,AddressLine1 text,AddressLine2 text,City text,PostalCode text);
CREATE TEMPORARY TABLE TPerson (PersonID integer,Title text,FirstName text,LastName text);
CREATE TEMPORARY TABLE TCustomer (CustomerID integer,PersonID integer);
CREATE TEMPORARY TABLE TPersonAddress (PersonID integer,AddressID integer);

Temporary tables have copied data from CSV files. The problem is that each person can have many different Addresses. So I have to load all the address to an AddressType Array. But that seems impossible right now and I can't find a way to handle the array.

All I found around the internet to handle the array is:

INSERT INTO aa VALUES (3, ARRAY[ARRAY[1,2],ARRAY[3,4]]);
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

And such but these example just add specified values that don't help with my case at all. So how to I make it copy values from: TAddress.AddressID,TAddress.AddressLine1,TAddress.AddressLine2,TAddress.City,TAddress.PostalCode

to array:

Person.Address.AddressID,Person.Address.AddressLine1,Person.Address.AddressLine2,Person.Address.City,Person.Address.PostalCode

And if it's the same person it has to add all the addresses to the same array.

Answer 1

About the error

While it is possible to assign subfields of composite / row types like this:

CREATE TYPE foo AS (a int, b text);
CREATE TABLE bar (c int, f foo);

Both work:

INSERT INTO bar(a,b) SELECT 1, '(1,foo)';
INSERT INTO bar(a,b.a, b.b) SELECT 2, 2, 'bar';

The same is not possible with arrays (so neither for arrays of composite types).

CREATE TABLE baz (a int, b foo[]);

Not possible:

INSERT INTO baz(a,b.a, b.b) SELECT 2, 2, 'bar';

Must be one of:

INSERT INTO baz(a,b) SELECT 1, '{"(1,foo)"}';

INSERT INTO baz(a,b) SELECT 1, ARRAY['(1,foo)'];

Database design

I'm going to suggest a complete redesign with normalized tables.

Arrays of composite types are very unwieldy, don't save any disk space, disable many of the core features for indexing or relational integrity and are really an exotic use of the options provided by Postgres.

I would not operate with composite column types. I know you commented that you "need" to do it like that, but I don't see the point. Just because you can do it, doesn't mean it should be done. Use more or less normalized tables instead. The simple case with two tables:

CREATE TABLE person (
  person_id serial PRIMARY KEY
, title text
, firstname text
, lastname text
);

CREATE TABLE address (
  address_id serial PRIMARY KEY
, person_id int REFERENCES person
, addressline1 text
, addressline2 text
, city text
, postalcode text
);

A person can have many addresses, an address belongs to a single person. In reality, addresses are used by multiple persons, but it's often simpler to add a few addresses redundantly. If you have many like that, use a many-to-many implementation with an additional table person_address. Details here:

• How to implement a many-to-many relationship in PostgreSQL?