Question 113736 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – Error "42701" when try to create view

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;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search