Postgres用外键数组创建表

问题描述

我正在制作一个名为routes的表。我希望它能够包含在其中的航班列表。航班的细节在航班表中。我想航班是从航班表中的外键ID数组。所以，我有这样的代码：

pre $ code $ CREATE TABLE routes
序列主键
flight integer [] ELEMENT REFERENCES航班，
用户CHARACTER VARYING（50）
）;

但是，它会给出错误：

<$语法错误在元素或附近
行2：id序列主键，航班整数[] ELEMENT REFERENC ...

我已经使用了这个：
作为参考，但是当我复制这个语法的时候，会给出这个错误信息。

  CREATE TABLE races（
 race_id integer PRIMARY KEY， 
标题文本，
 race_day DATE，
 ... 
 practice1_positions integer [] ELEMENT REFERENCES驱动程序，
 practice2_positions integer [] ELEMENT REFERENCES驱动程序，
 practice3_positions integer [] ELEMENT REFERENCES驱动程序，
 qualifying_positions integer [] ELEMENT REFERENCES驱动程序，
 final_positions integer [] ELEMENT REFERENCES驱动程序
）; 
  

解决方案

您链接到的博客显然只是一个补丁这是为9.3提出的。它没有把它变成9.3和9.4
你可以并尝试自行修补安装，或者您可以更改模式。

如果您想了解什么发生这个功能，你可能应该邮件给作者。

I am making a table named "routes". I want it to be able to contain a list of flights in it. The flights' details are in the flights tables. I want "flight" to be an array of foreign key ids from the flights table. So, I have this code:

CREATE TABLE routes (
id SERIAL PRIMARY KEY,
flight integer[] ELEMENT REFERENCES flights,
user CHARACTER VARYING(50)
);

But, it gives the error:

ERROR:  syntax error at or near "ELEMENT"
LINE 2:     id SERIAL PRIMARY KEY, flight integer[] ELEMENT REFERENC...

I am using psql (9.3.10)

I have used this:http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ as a reference, but when I copy that syntax it gives this error.

This is the syntax that I am using as a reference:

CREATE TABLE races (
   race_id integer PRIMARY KEY,
   title text,
   race_day DATE,
   ...
   practice1_positions integer[] ELEMENT REFERENCES drivers,
   practice2_positions integer[] ELEMENT REFERENCES drivers,
   practice3_positions integer[] ELEMENT REFERENCES drivers,
   qualifying_positions integer[] ELEMENT REFERENCES drivers,
   final_positions integer[] ELEMENT REFERENCES drivers
);

解决方案

The blog you link to states clearly it's just a patch that was proposed for 9.3. It didn't make it into 9.3 nor 9.4You can comb the mailing list and try to patch your installation yourself or you can just change your schema.

If you want the details on what happened to that feature you probably should mail the author.

这篇关于Postgres用外键数组创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！