Thread: PostgreSQL JDBC and sub-select
Hello, I work with JDeveloper and PostgreSQL JDBC and I have one problem. I get error : sub-SELECT in FORM must have an alias I can't change SQL command, but it is internal JDeveloper command Is it SQL standard (must have alias) or PostgreSQL specific ? Regards Haris Peco
On Sat, 9 Nov 2002, snpe wrote: > Hello, > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > I get error : > sub-SELECT in FORM must have an alias > I can't change SQL command, but it is internal JDeveloper command > > Is it SQL standard (must have alias) or PostgreSQL specific ? It looks to me to be standard. I think the appropriate portion of the grammar is: <table reference> := <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] <derived table> := <table subquery> <correlation name> := <identifier>
snpe <snpe@snpe.co.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
snpe kirjutas L, 09.11.2002 kell 22:51: > Hello, > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > I get error : > sub-SELECT in FORM must have an alias > I can't change SQL command, but it is internal JDeveloper command You could set up query logging in the backend and see what the offending query is. It may still be something you did (a missing or extra something somewhere). --------------- Hannu
On Sunday 10 November 2002 05:27 am, Tom Lane wrote: > snpe <snpe@snpe.co.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". I didn't say that.I asked if that is PostgreSQL specific.Thanks for answer. regards Haris Peco
On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote: > snpe kirjutas L, 09.11.2002 kell 22:51: > > Hello, > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > I get error : > > sub-SELECT in FORM must have an alias > > I can't change SQL command, but it is internal JDeveloper command > > You could set up query logging in the backend and see what the offending > query is. It may still be something you did (a missing or extra > something somewhere). > How ? regards Haris Peco
Hello,Query :
select id i from tab
don't work in PostgreSQL
I know that
select id as i from tab
work, but I think that AS in 'as cluse' is optional
Please comment.
regards
Haris Peco
> > You could set up query logging in the backend and see what the offending > > query is. It may still be something you did (a missing or extra > > something somewhere). > > > How ? These settings have worked for me in a similar situation: (pulled from the admin list archives) <snip> My goal was to get all of the SQL statements from a JDBC front-end to be logged as they are executed in the postgres.log file (and not in the syslog.) Adding the following to my postgresql.conf did the job: syslog = 0 silent_mode = off debug_print_query = on debug_pretty_print = on I'm not sure if the pretty print option does anything for the SQL, but it didn't hurt. </snip> The results appear on /var/log/postgresql.log using the Debian Linux distribution. Not sure of the location in others. -Nick
On Sun, 2002-11-10 at 10:41, snpe wrote: > Hello, > Query : > > select id i > from tab > > don't work in PostgreSQL > > I know that > > select id as i > from tab > > work, but I think that AS in 'as cluse' is optional Thats true. Section 7.11 has it as optional <derived column> ::= <value expression> [ <as clause> ] <as clause> ::= [ AS ] <column name> But this isn't going to be very nice to fix. conflicts: 1378 shift/reduce, 44 reduce/reduce -- Rod Taylor
snpe wrote: > work, but I think that AS in 'as cluse' is optional > > Please comment. See: http://developer.postgresql.org/docs/postgres/sql-select.html Near the bottom: " SQL92 SELECT Clause In the SQL92 standard, the optional keyword AS is just noise and can be omitted without affecting the meaning. The PostgreSQL parser requires this keyword when renaming output columns because the type extensibility features lead to parsing ambiguities in this context. AS is optional in FROM items, however." Joe
Joe Conway <mail@joeconway.com> quotes:
> In the SQL92 standard, the optional keyword AS is just noise and can be
> omitted without affecting the meaning. The PostgreSQL parser requires this
> keyword when renaming output columns because the type extensibility features
> lead to parsing ambiguities in this context. AS is optional in FROM items,
> however.
Actually, I think it's not so much datatype extensibility as operator
extensibility, and specifically the fact that we allow postfix
operators. If AS were optional, then
SELECT 1 + x FROM foo;
could be parsed either as "(1 + x)" (infix +, x presumably a column name)
or as "(1 +) x" (postfix +, x an AS-name).
So allowing AS to be optional would at minimum require taking out
postfix operators. There might be other features we'd have to lose,
too; I haven't tried messing with the grammar to see what would happen.
regards, tom lane
On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote: > snpe kirjutas L, 09.11.2002 kell 22:51: > > Hello, > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > I get error : > > sub-SELECT in FORM must have an alias > > I can't change SQL command, but it is internal JDeveloper command > > You could set up query logging in the backend and see what the offending > query is. It may still be something you did (a missing or extra > something somewhere). > query is like this : select * from (select * from tab) where 1=2 This is work with Oracle,DB2,SQL Server Postgresql request alias in sub-select in from clause regards Haris Peco
> On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote: > > snpe kirjutas L, 09.11.2002 kell 22:51: > > > Hello, > > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > > I get error : > > > sub-SELECT in FORM must have an alias > > > I can't change SQL command, but it is internal JDeveloper command > > > > You could set up query logging in the backend and see what the offending > > query is. It may still be something you did (a missing or extra > > something somewhere). > > > > query is like this : > select * from (select * from tab) where 1=2 > > This is work with Oracle,DB2,SQL Server > Postgresql request alias in sub-select in from clause The SQL standard requires that you alias it. Postgres follows the standard on this: select * from (select * from tab) as sub where 1=2; Chris