SQL subquery fails in Spark 2 when loading PostgreSQL table

Question

I am facing a very annoying PSQL issue when trying to load part of a PostgreSQL table via a subquery.

The query is :

SELECT 
    N1,
    N2, 
    N3,
    N4
FROM CORR 
WHERE CORR_N5 >= (now() - interval '18 year') 
AND CORR_N5 <= (now() - interval '18 year' + interval '1 month')

This one works if written directly in PgAdmin. However when I run it from a spark 2 job, I get tho following error message :

org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias
  Hint: For example, FROM (SELECT ...) [AS] foo.

Even when I put an alias after all the clauses, the same issue happens.

Any advice ?

Thanks in advance

score 0 · Answer 1 · edited Sep 28 '18 at 11:53

0

Melvin, have a look at the below links

https://pganalyze.com/docs/log-insights/app-errors/U115

subquery in FROM must have an alias

SELECT * FROM (
    SELECT N1, N2, N3, N4 
    FROM CORR WHERE COR_N5 >= (now() - interval '18 year') 
    AND CORR_N5 <= (now() - interval '18 year' + interval '1 month')
) AS input

edited Sep 28 '18 at 11:53

Melvin C.

13
2

answered Sep 27 '18 at 18:43

Mansoor Baba Shaik

492
2
6

Thank you very much Mansoor, my issue was solved by your input and a simple upgrade of my postgre driver's version. Have a great weekend. Melvin – Melvin C. Sep 28 '18 at 10:35

SQL subquery fails in Spark 2 when loading PostgreSQL table

1 Answers1