I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.

It appears to me that this type does not allow to compose a type out of relations/tables. In other words, I can do this:

CREATE TYPE compfoo AS (f1 int, f2 text);

but I can't do

CREATE TYPE compfoo AS (f1 relation, f2 relation);

I was wondering if my understanding is correct, and if not, how one could achieve the latter?

I want to indicate that I don't have any particular application for it. I would actually be interested in starting a discussion on an application! I am just trying to expand my understanding of this feature which is often touted as a very powerful feature in the extensible DBMS world.

  • 1
    what do you mean? can you give an example?
    – Jasen
    Jul 6, 2019 at 23:16
  • "CREATE TYPE compfoo AS (f1 relation, f2 relation);" For some reason it feels like you are looking for Inheritance instead Oct 21, 2019 at 19:00
  • .. also it feels like you are looking for MULTISET (ANSI/ISO SQL 2003) Oct 21, 2019 at 19:07

1 Answer 1


The old version with support is 9.4 today and i can achieve your case, and then in the recent version too.

for example:

create table a ( i int, j int);
create table b ( k text, l text);
CREATE TYPE compfoo AS (f1 a, f2 b);
create table c (m compfoo );
insert into c values ( row(row(1,2),row('hello','world'))   );
select * from c;


you can check this test in https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=4499c5d52e876979d81fb3f2537c2439

check your version

  • I'm talking about passing a table or relation type instead of a table. Sorry if unclear.
    – Zeruno
    Jul 6, 2019 at 19:45
  • In PostgreSQL a table automatically becomes a data type Jul 7, 2019 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.