1

I have created two types:

Create  Type info_typ_1 AS (
Prod_id integer, 
category integer);

Create Type movie_typ AS(
title varchar(50),
actor varchar(50),
price float);

And I want to create a table that consists of these two types. I know that for a table that consists of one type, it's:

CREATE TABLE Table1 of type1
(
  primary key(prod_id)
);

Is there any way to do that for the two types I created above?

What I tried doing(which is wrong), is creating a third type that contains the first two:

Create Type info_ AS (
info info_typ_1,
movie movie_typ);

and then creating the table:

CREATE TABLE table1 of info_
(
  primary key(Prod_id)
);

But it doesn't work. I get this error:

ERROR:  column "prod_id" named in key does not exist
LINE 3:   primary key(Prod_id)
          ^
********** Error **********

ERROR: column "prod_id" named in key does not exist
SQL state: 42703
Character: 43
Shevliaskovic
  • 1,562
  • 4
  • 26
  • 43

1 Answers1

3

You cannot make prod_id the primary key of table1 because the only columns are the two composite types info and movie. You cannot access the base types of these composite types in a PRIMARY KEY clause.

What you were trying to do works with a pk constraint on info or movie.
Except, it's probably not what you were looking for, which is not possible this way.

You could implement something like this with ...

Inheritance

Here you can inherit from multiple parent tables (substitute for your types). Example:

CREATE TABLE info (
  prod_id integer
 ,category integer
);

CREATE TABLE movie (
   title text
  ,actor text
  ,price float
);

CREATE  TABLE movie_info (
   PRIMARY KEY(prod_id)             -- now we can use the base column!
)
INHERITS (info, movie);

INSERT INTO movie_info (prod_id, category, title, actor, price)
VALUES (1, 2, 'who donnit?', 'James Dean', '15.90');

SELECT * FROM movie_info;

-> SQLfiddle demonstrating both.

Be sure to read about limitations of inheritance in the manual.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • is there any way to do this with `type` ? Instead of creating three tables – Shevliaskovic Feb 05 '14 at 08:06
  • @Shevliaskovic: Inheritance requires tables. I don't see any harm in creating tables, though. You could even store those away in some dedicated schema. [Like demonstrated in this related answer.](http://stackoverflow.com/questions/20575610/retrieve-all-records-from-multiple-schemas-in-a-postgresql-database/20576324#20576324) – Erwin Brandstetter Feb 05 '14 at 14:09