Search code examples
sqldatabasepostgresqljoinview

ERROR: column "id" specified more than once - when creating view

I wrote this code and it works:

SELECT d1.*,d3.*
FROM bdir_programmation.d_program AS d1 
JOIN (
    SELECT d_program.id,MAX(id_reg) AS maxv
    FROM bdir_programmation.d_program
    GROUP BY d_program.id) AS d2 ON d1.id=d2.id AND d1.id_reg=d2.maxv
JOIN bdir_programmation.d_infra_progra d3 ON d1.id=d3.id;

But when I want to create a View, I get this error:

ERROR: column "id" specified more than once

I tried to rename different variables in the SELECT list, as explained in others topics, but in this case it seems it was not made in a correct way. I have some difficulties with the structure, it's totally new for me.

Solution

  • Both d_program and d_infra_progra have a column named id. This works for a query, where two columns can have the same alias name, but not for a view, where column names must be unique.

    Since the values for both columns are the same, take only one of them. This requires that you do away with the * and enumerate the columns you need instead, giving them aliases if you wish.