Answer the question
In order to leave comments, you need to log in
How to get data from multiple related tables in Postgres?
Postgres 9.4
4 tables are linked many-to-many. I created an additional table to implement these relationships:
CREATE TABLE "public"."relation" (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"table1" uuid NOT NULL,
"table2" uuid,
"table3" uuid,
"table4" uuid,
"approved" bool DEFAULT true,
CONSTRAINT "relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "table1" FOREIGN KEY ("table1") REFERENCES "public"."table1" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "table2" FOREIGN KEY ("table2") REFERENCES "public"."table2" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "table3" FOREIGN KEY ("table3") REFERENCES "public"."table3" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "table4" FOREIGN KEY ("table4") REFERENCES "public"."table4" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."relation" OWNER TO "postgres";
SELECT t.*
FROM ( SELECT table1.id,
(select row_to_json(relations.*) as array_to_json
from(select * from relation where table1 = table1.id) relations
) as relations,
from public.table1) t
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question