Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres creating a Table with an array of foreign keys

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
);
like image 901
Rorschach Avatar asked Dec 08 '15 13:12

Rorschach


1 Answers

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.4 You 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.

like image 126
Jakub Kania Avatar answered Oct 28 '22 09:10

Jakub Kania