postgresql error near as in java

Question

I have a PostgreSQL 9.2.2 server running for a GWT project.

One query is giving trouble, as it works fine, when I try it in the sql tab in phpPgAdmin:

 (select distinct max(enddate) as max,meterid 
  from usermeasurements 
  where locationid=127025 
    AND usage>-1 
  GROUP BY meterid ) as t1 
  left outer join usermeasurements as t2 
            ON (t1.meterid=t2.meterid 
            AND t1.max=t2.enddate)

But when I try using the query in my gwt project, I get the following error:

 [btpool0-5] ERROR com.example.DataGetter  - unable to get usermeasurement: org.postgresql.util.PSQLException: ERROR: syntax error at or near "as" Position: 112

It's the first time I have experienced a difference between what works in phpPgAdmin and an app.

As can be seen, I use BoneCP to handle my connection pool, but that shouldn't have any effect as far a I know.

Hint for debugging: turn on full sql logging, so you can see the real sql query stackoverflow.com/questions/722221/… — leonbloy
– leonbloy, Commented Jan 11, 2013 at 15:28
@MortenSkov It cant be the full query. It is only the FROM part of the bigger query. — Ihor Romanchenko
– Ihor Romanchenko, Commented Jan 11, 2013 at 16:35
@leonbloy, I tried turning it on, and got this: (select max(enddate) as max, meterid from usermeasurements where locationid=$1 AND usage>-1 GROUP BY meterid ) as t1 left outer join usermeasurements as t2 ON(t1.meterid=t2.meterid AND t1.max=t2.enddate) — MsJorge
– MsJorge, Commented Jan 11, 2013 at 16:42
@IgorRomanchenko, well no, it is actually the whole query. I know that my style might not be the most beautiful, but its true :) If I C/P the query from the OP into pgAdmin, it works as it should — MsJorge
– MsJorge, Commented Jan 11, 2013 at 16:43

someone · Accepted Answer · 2013-01-11 16:10:01Z

3

Since max is a keyword use something else after as

select distinct max(enddate) as mymax, ......

UPDATED :

Another thing. In your exception it says

unable to get usermeasurement:

but in your sql you have used usermeasurements (You have a s in the end). Check your source code again there may be you have missed letter s

edited Jan 11, 2013 at 16:10

answered Jan 11, 2013 at 15:25

someone

6,5728 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jonathan.cone Over a year ago

Good catch, I think you can use AS "max" as well.

MsJorge Over a year ago

@guess_me I just checked, and the spelling is correct. I even tried C/P it from the code to phpPgAdmin and it worked flawlessly

someone Over a year ago

@MortenSkov hmmm but I'm just wandering why it says error in without letter s

Ihor Romanchenko · Accepted Answer · 2013-01-11 16:41:23Z

0

Maybe the query should look like:

SELECT * 
FROM
(SELECT DISTINCT max(enddate) as max, meterid 
 FROM usermeasurements 
 WHERE locationid = 127025 
   AND usage > -1 
 GROUP BY meterid ) as t1 
LEFT JOIN usermeasurements as t2 
     ON (t1.meterid=t2.meterid 
         AND t1.max=t2.enddate)

The query in the question isn't correct (it looks like a FROM part of the bigger query).

answered Jan 11, 2013 at 16:41

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

7 Comments

MsJorge Over a year ago

hmm....when I try to paste that into PgAdmin I get an similar error: ERROR: syntax error at or near "AS" LINE 9: ON (t1.meterid=t2.meterid) AS sub

Ihor Romanchenko Over a year ago

@MortenSkov But there is no AS sub in the query. Where are you getting it from?

Ihor Romanchenko Over a year ago

Maybe you have some query rewrite RULE on the tables? Or one of the tables is a VIEW ?

MsJorge Over a year ago

Naahh...It should all be pretty standard. I just installed the server before I started this project.

Ihor Romanchenko Over a year ago

@MortenSkov The query in the question is syntactically incorrect. See the manual here.

|

Collectives™ on Stack Overflow

postgresql error near as in java

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related