Thread: postgres question: Views with duplicate field names
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
);
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 |
--------------+----+----------
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?
Thanks!![](https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif)
![](https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif)
Ryan
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
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 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
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
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.