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

Question

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.

Also, this should be on [DBA](https://dba.stackexchange.com/) — Elikill58, Apr 06 '23 at 06:32

score 2 · Accepted Answer · answered Apr 05 '23 at 14:58

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.

Erwin Brandstetter · Answer 2 · 2023-04-05T15:23:25.107

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:

How to drop one join key when joining two tables

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:

Select first row in each GROUP BY group?

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

Optimize groupwise maximum query

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.

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

2 Answers2