Thread: How do I insert data into a table within a table

How do I insert data into a table within a table

From
Stan Raby
Date:
--
As I understand it when you create a table in postgres that it is automatically
a type. Therefore you can then the declare an attribute of this type in another
table e.g

create table person
(
  first_name  char(20),
  second_name char(20)
);

create table couple
(
  man   person,
  woman person
);

The couple table can be queried without any problems e.g.

select * from couple;

returns :

man|woman
---+-----
(0 rows)

select couple.man.first_name, couple.man.second_name from couple;

returns :

first_name|second_name
----------+-----------
(0 rows)

But how do you put data into the couple table ?
I have tried a number of methods but without any success.

e.g.
insert into
couple(couple.man.first_name,couple.man.second_name,couple.woman.first_name,couple.woman.second_name)
values('Fred','Smith','Jane','White');

insert into
couple(man(first_name,second_name),woman(first_name,second_name))
values('Fred','Smith','Jane','White');

insert into
couple.man.first_name,couple.man.second_name,couple.woman.first_name,couple.woman.second_name
values('Fred','Smith','Jane','White');

insert into
couple(man.first_name,man.second_name,woman.first_name,woman.second_name)
values('Fred','Smith','Jane','White');

All of these (plus others) all produce parser errors.
e.g. ERROR :  parser: parse error at or near "."

Thankyou in advance for any help.

------------------------------------
Stan Raby
Computer Park Limited
Tel: +44 1536 417155
Fax: +44 1536 417566
email: sraby@computerpark.co.uk