Re: [HACKERS] PostgreSQL JDBC and sub-select

snpe <snpe(at)snpe(dot)co(dot)yu> writes:
> I work with JDeveloper and PostgreSQL JDBC and I have one problem.
> I get error :
> sub-SELECT in FORM must have an alias

> Is it SQL standard (must have alias) or PostgreSQL specific ?

The SQL standard says you must write an alias. A FROM item is a <table
reference> (SQL92 section 7.4), which is defined (in SQL92 6.3) as

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

where

<derived table> ::= <table subquery>

The square brackets show that an alias (<correlation name>) is optional
for a plain table name, but is required for a sub-SELECT (<derived
table>).

This is not just a random whim on the part of the SQL spec writers.
One reason why they did it that way is to ensure that some specific
<correlation name> can be associated with every column produced by a
FROM clause. I used to remember some other interesting consequences,
but it's too late on a Saturday night to recall all the details...

> I can't change SQL command, but it is internal JDeveloper command

I have zero sympathy for "please change Postgres because my application
cannot be bothered to comply with the SQL specification".

regards, tom lane