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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>