Two columns with the same alias in Postgres

Question

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.

Answer 1

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