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

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    A view cannot produce a derived table with duplicate column names, like Laurenz explained.

    You could simplify the query with a USING clause in the join to swallow duplicate (identical, and hence noise anyway) columns.

    SELECT *
FROM   bdir_programmation.d_program d1 
JOIN  (
    SELECT d.id, MAX(d.id_reg) AS id_reg  -- instead of maxv
    FROM   bdir_programmation.d_program d
    GROUP  BY 1
    ) d2 USING (id, id_reg)
JOIN   bdir_programmation.d_infra_progra d3 USING (id);

    Works, unless there are more identical names.

    See:

    But, typically, this is substantially simpler and cheaper with DISTINCT ON:

    SELECT *
FROM  (
   SELECT DISTINCT ON (id) *
   FROM   bdir_programmation.d_program AS d1 
   ORDER  BY d.id_reg DESC NULLS LAST
   ) d1
JOIN   bdir_programmation.d_infra_progra d3 USING (id);

    See:

    The sort order DESC NULLS LAST producing the the equivalent of max(). See:

    Again, the USING clause swallows the second, identical instance of the id column.

    Either way, the view definition translates SELECT * into the iterative list of columns produced by the query at creation time ("early binding"). Later additions to the underlying tables will not be reflected in the view.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search