Re: SQL gotcha

Στις Tuesday 15 February 2011 01:29:25 ο/η Dean Gibson (DB Administrator) έγραψε:
> As you all know, when you specify an alias for a table or column name,
> the keyword "AS" is usually optional. I personally like to always
> include the "AS" keyword, if for no other reason than readability, and I
> presume that always using "AS" might catch some syntactic errors as well.
>
> However, recently I ran across a statement where the optionality of the
> "AS" keyword caused me some grief:
>
> SELECT ... FROM aaa LEFFT JOIN bbb WHERE ...
>
> Note the (unintentional) misspelling of the keyword "LEFT". PostgreSQL
> took "LEFFT" to be an alias for table "aaa" and silently accepted the
> statement; naturally I did not get the results I expected !!
>
> If PostgreSQL had an option to always require the "AS", that would have
> caught this error. Is there any interest in PostgreSQL having any
> "pedantic"-type options, like many C/C++ compilers do?
>

Actually PostgreSQL seems to endorse the usage of "AS" more than the SQL standard requires.
http://www.postgresql.org/docs/8.3/interactive/sql-select.html

"The AS Key Word

In the SQL standard, the optional key word AS is just noise and can be omitted without affecting the meaning.
The PostgreSQL parser requires this key word when renaming output columns because the type extensibility features
lead to parsing ambiguities without it. AS is optional in FROM items, however. "

--
Achilleas Mantzios