PostgreSQL – Error "42701" when try to create view

November 16, 2022

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;

>Solution :

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.