在PostgreSQL中创建两种类型的表 [英] Create a table of two types in PostgreSQL

问题描述

我创建了两种类型：

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);

然后创建表：

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

推荐答案

您不能将 prod_id 用作 table1 的主键，因为唯一的列是两个组合类型 info 和 movie 。您不能在 PRIMARY KEY 子句中访问这些组合类型的基本类型。

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.

您要尝试执行的操作在 info 或 movie 上具有pk约束。

除外，这可能不是您所需要的

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.

您可以使用...

在这里您可以从多个父表继承（替代您的类型）。示例：

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 展示了两者。

-> SQLfiddle demonstrating both.

请务必阅读有关手册中的继承限制。

这篇关于在PostgreSQL中创建两种类型的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！