Why is it allowed to query
SELECT
'foo' "baz",
'bar' "baz"
in Postgres (tested on 9.6)? In which situations does this make sense? On the first look this just seems like a great source of bugs.
-
you are hard coding alias. The query is sound what you call the columns is up to you. This is a 'developer' issue and not really an issue with Postgres.Gurmokh– Gurmokh2018-06-19 09:32:28 +00:00Commented Jun 19, 2018 at 9:32
-
It's not a source of bugs if you have to explicitly give it a duplicate alias.404– 4042018-06-19 09:33:15 +00:00Commented Jun 19, 2018 at 9:33
-
I believe the trick is to avoid such an approach. Although it's hard for me to imagine, there are certainly use cases that demand such alias duplication. So, PostgreSQL is actually quite flexible to allow it :)Jim Jones– Jim Jones2018-06-19 10:05:44 +00:00Commented Jun 19, 2018 at 10:05
Add a comment
|
1 Answer
That's a user's responsibility for how the data will be presented. There are two typical cases in which a novice should be attentive. The first one concerns anonymous columns, e.g.:
select 'alfa', 'beta'
?column? | ?column?
----------+----------
alfa | beta
The second one concerns joining tables with the same column names, e.g.
select *
from company c
join employee e on company_id = c.id
id | name | id | name | company_id
----+---------+----+-------+------------
1 | Company | 1 | Smith | 1
In both cases one should use aliases to make the column names unambiguous and more informative. And I always do this in my queries which can be read by others. However, when I'm querying a database ad hoc I'm sometimes thankful that Postgres is not too restrictive and I don't have to write down several column names instead of asterisk.
Most importantly, Postgres checks whether column names are unique in cases where this is really relevant and never allows data integrity violation, e.g.:
create view my_view as
select 'foo' as "baz", 'bar' as "baz"
ERROR: column "baz" specified more than once
1 Comment
vektor
However, when I'm querying a database ad hoc I'm sometimes thankful that Postgres is not too restrictive and I don't have to write down several column names instead of asterisk. This. Thanks, accepted!