Re: postgres question: Views with duplicate field names

Interesting, thanks!  Do you know why the first one fails instead of doing that renaming process, while your version succeeds?

On Monday, September 5, 2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 09/05/2016 12:55 PM, Ryan Murphy wrote:

Hello, I have a question about views in Postgres.

Given a table like so:

create table todo (
  id serial,
  task text,
  done_time timestamp default null
);

it is legal (though perhaps not advised, by some) to query it like so:

select task, * from todo;

This gives a result with 2 redundant "task" fields (with duplicate names):

     task     | id |     task     | done_time
--------------+----+--------------+-----------
 wash the dog |  1 | wash the dog |

However, if I try to make a view of this I hit a problem: views can't
have duplicate field names:

create view task2 as   select task, * from todo;

ERROR:  column "task" specified more than once

I understand this may seem like a silly thing to want to do, but my
question is if there is an easy way to automatically de-dup the columns
of the query so I can create a view from it.  Or is there any
fundamental reason why views can't be allowed to have duplicate columns,
just like the result set above?

test=> create view task2 as   select task AS task_1 , * from todo;
CREATE VIEW

test=> \d task2
                 View "public.task2"

  Column   |            Type             | Modifiers

-----------+-----------------------------+-----------

 task_1    | text                        |

 id        | integer                     |
 task      | text                        |
 done_time | timestamp without time zone |

Thanks!

Ryan

--
Adrian Klaver
adrian.klaver@aklaver.com