pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL gotcha
2+ messages / 2 participants
[nested] [flat]
* SQL gotcha
@ 2011-02-14 23:29 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2011-02-15 07:41 ` Re: SQL gotcha Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dean Gibson (DB Administrator) @ 2011-02-14 23:29 UTC (permalink / raw)
To: pgsql-sql
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?
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL gotcha
2011-02-14 23:29 SQL gotcha Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2011-02-15 07:41 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Achilleas Mantzios @ 2011-02-15 07:41 UTC (permalink / raw)
To: pgsql-sql; +Cc: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
Στις 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-02-15 07:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-14 23:29 SQL gotcha Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2011-02-15 07:41 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox