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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Ok, that makes sense. Thanks!

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

Ok, that makes sense. Thanks!

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

Ok, totally. I missed that when I first read your query, didn't read it
closely enough. Thanks.