Thread: postgres question: Views with duplicate field names

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

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

On 09/05/2016 01:13 PM, Ryan Murphy wrote:
> Interesting, thanks!  Do you know why the first one fails instead of
> doing that renaming process, while your version succeeds?

Because I specifically aliased the first task reference using AS task_1.

>
> On Monday, September 5, 2016, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto: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
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Ryan Murphy <ryanfmurphy@gmail.com> writes:
> Interesting, thanks!  Do you know why the first one fails instead of doing
> that renaming process, while your version succeeds?

You're confused about the input vs. the output.  The output columns
of a view all have to have distinct names, just like you can't do
"create table foo (f1 int, f1 int)".  They can be reading the same
values, though.

            regards, tom lane

You're confused about the input vs. the output.  The output columns
of a view all have to have distinct names, just like you can't do
"create table foo (f1 int, f1 int)".  They can be reading the same
values, though.

                        regards, tom lane

You're confused about the input vs. the output.  The output columns
of a view all have to have distinct names, just like you can't do
"create table foo (f1 int, f1 int)".  They can be reading the same
values, though.

                        regards, tom lane