PostgreSQL - Error "42701" when try to create view-CodePudding

I try to create a view which contains calculated data from others table. However, I have this error message: [42701] ERROR: column "id" specified more than once.

My SQL script:

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id,
       c.id
   FROM commune "c"
       JOIN departement "d" ON d.id = c.departement_id
       JOIN bureau_de_vote "bv" ON bv.commune_id = c.id
       LEFT JOIN scrutin_bureau "sb" ON sb.bureau_de_vote_id = bv.id
       LEFT JOIN resultat_scrutin_bureau "rsb" ON rsb.scrutin_bureau_id = sb.id
       LEFT JOIN resultat_scrutin_bureau "rsb_t" ON (
           rsb_t.scrutin_bureau_id = sb.id
           AND rsb_t.etat_id = (SELECT id FROM etat WHERE code = 'transmission')
       )
       JOIN election ON sb.election_id = election.id
   GROUP BY c.id, d.id;

CodePudding user response：

You are selecting

d.id, c.id

Both columns cannot be present in the view with the name id - you'll need to give at least one of them an alias, probably both with an alias name that describes what the id represents.

CodePudding user response：

As Error specified you try to create two columns with name id in your query.

very simplified example of incorrect view creation with same error:

CREATE VIEW a AS
SELECT 1 AS hello, 2 AS hello;

And correct one

CREATE VIEW a AS
SELECT 1 AS hello, 2 AS world;

So in order to fix your query change

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id,
       c.id

into

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id AS departement_id,
       c.id AS commune_id