imitatio creatio co we łbie piszczy

column number as column alias in postgres

I have seen such queries many times:


SELECT foo, bar, (some long and complicated SQL subexpression), count(*)
FROM somewhere
GROUP BY foo,bar, (some long and complicated SQL subexpression)
ORDER BY count(*) DESC


This has some drawbacks:

• you have to repeat exactly the same expression two times, which makes query longer
• it is easier to make a mistake while editing the subexpression

Did you know there's a neat shortcut for this in postgres?


SELECT foo, bar, (some long and complicated SQL subexpression), count(*)
FROM somewhere
GROUP BY 1,2,3
ORDER BY 4 DESC


This is known as column number aliases.
I have no idea if this comes from SQL standard or not.
But it saves me quite a lot of typing :)

I know it works at least in postgresql, mysql and informix.
Does it work in your database, too?