Postgres Complex Select in a View

Question

I have this select clause that is working perfect:

SELECT 
    "Aspectos"."ID" AS "Aspecto Normativo ID", 
    "Aspectos"."Aspecto" AS "Aspecto Normativo",        
    "Fatores"."ID", "Fatores"."Fator" AS "Fator Normativo",
    "Diagnostico"."Vinculo_Final", 
    "Fatores_1"."ID", 
    "Fatores_1"."Fator" AS "Fator Determinativo", 
    "Aspectos_1"."ID" AS "Aspecto Determinativo ID", 
    "Aspectos_1"."Aspecto" AS "Aspecto Determinativo", 
    Count("Itens"."ID") AS "No Itens", 
    Count("Itens"."ID") AS "Pri"
FROM "Diagnostico" INNER JOIN ("Aspectos" AS "Aspectos_1" 
INNER JOIN (("Fontes" INNER JOIN "Itens" ON "Fontes"."ID" = "Itens"."Fonte") 
INNER JOIN ("Fatores" AS "Fatores_1" 
INNER JOIN ("Aspectos" 
INNER JOIN ("Vinculos" 
INNER JOIN "Fatores" 
ON "Vinculos"."Fator_Normativo" = "Fatores"."ID") 
ON ("Aspectos"."ID" = "Fatores"."Aspecto") 
AND ("Aspectos"."ID" = "Fatores"."Aspecto")) 
ON "Fatores_1"."ID" = "Vinculos"."Fator_Determinativo") 
ON "Itens"."ID" = "Vinculos"."Item") 
ON "Aspectos_1"."ID" = "Fatores_1"."Aspecto") 
ON "Diagnostico"."ID" = "Vinculos"."Diagnostico_ID"
GROUP BY "Aspectos"."ID", "Aspectos"."Aspecto", 
          "Fatores"."ID", "Fatores"."Fator", 
          "Diagnostico"."Vinculo_Final", 
          "Fatores_1"."ID", 
          "Fatores_1"."Fator", 
          "Aspectos_1"."ID", 
          "Aspectos_1"."Aspecto"
ORDER BY  "Aspectos"."ID", "Aspectos_1"."ID", 
         "Fatores"."Fator", "Fatores_1"."Fator";

But when I try to CREATE A VIEW with this same select I'm getting thuis error:

ERROR: column "ID" specified more than one time

Can anybody help me on this. Thanks

Can you show us your CREATE VIEW statement?

user330315
– user330315

2013-02-20 16:19:46 +00:00
Commented Feb 20, 2013 at 16:19 — user330315
– user330315, Commented Feb 20, 2013 at 16:19

vyegorov · Accepted Answer · 2013-02-20 20:06:13Z

2

You have "Fatores"."ID" (line 4) and "Fatores_1"."ID" (line 6). Give them different aliases.

For such complex queries it is recommended to have only 1 (one) column in per line in the statement for better visibility. Also it is recommended to always give aliases to the columns.

edited Feb 20, 2013 at 20:06

answered Feb 20, 2013 at 16:40

vyegorov

23k7 gold badges61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Britto Over a year ago

It's running perfectly! Thank you again vyegorov!

Collectives™ on Stack Overflow

Postgres Complex Select in a View

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related