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 http://stackoverflow.com/questions/722221/how-to-log-postgres-sql-queries — leonbloy, Jan 11 '13 at 15:28
@MortenSkov It cant be the full query. It is only the `FROM` part of the bigger query. — Ihor Romanchenko, Jan 11 '13 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, Jan 11 '13 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, Jan 11 '13 at 16:43

someone · Answer 1 · 2013-01-11T16:10:01.637

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 '13 at 16:10

answered Jan 11 '13 at 15:25

someone

6,577
7
37
60

Good catch, I think you can use `AS "max"` as well. – jonathan.cone Jan 11 '13 at 15:35
@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 – MsJorge Jan 11 '13 at 16:14
@MortenSkov hmmm but I'm just wandering why it says error in without letter s – someone Jan 11 '13 at 16:20

score 0 · Accepted Answer · answered Jan 11 '13 at 16:41

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 '13 at 16:41

Ihor Romanchenko

26,995
8
48
44

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 – MsJorge Jan 11 '13 at 16:47
@MortenSkov But there is no `AS sub` in the query. Where are you getting it from? – Ihor Romanchenko Jan 11 '13 at 16:50
Maybe you have some query rewrite `RULE` on the tables? Or one of the tables is a `VIEW` ? – Ihor Romanchenko Jan 11 '13 at 16:51
Naahh...It should all be pretty standard. I just installed the server before I started this project. – MsJorge Jan 11 '13 at 16:53
@MortenSkov The query in the question is syntactically incorrect. See the manual [here](http://www.postgresql.org/docs/current/static/sql-select.html). – Ihor Romanchenko Jan 11 '13 at 16:54
Maybe you can set up this error case in [SQLFiddle](http://sqlfiddle.com/#!12). I cant reproduce it muself. – Ihor Romanchenko Jan 11 '13 at 16:56
@MortenSkov My guess, that something (like phpPgAdmin) wraps another `SELECT * FROM () as sub` around the query, making it syntactically correct. Try the native PostgreSQL pgAdmin GUI tool or command line psql. – Ihor Romanchenko Jan 11 '13 at 17:08

postgresql error near as in java

2 Answers2