CODEWITHSUNDEEP
ruby-on-railspostgresqlherokusqlite
Acrux
Acrux

Reputation: 406

Converting sqlite3 query to postgres

I just uploaded my site to Heroku however I got an error in one of my controllers. Apparently I have a syntax error with my postgres code. This works in sqlite3 but when I converted it to postgres a syntax error occurred. 

   @cars=Car.find_by_sql("SELECT cars.*, CASE WHEN EXISTS (SELECT book_cars.* FROM book_cars WHERE book_cars.car_id=cars.id AND book='t') THEN 'TRUE' ELSE 'FALSE' END  AS 'is_book' FROM cars WHERE is_book='FALSE'")

Any ideas as to what caused the error? The error code: 

PG::SyntaxError: ERROR:  syntax error at or near "'is_book'"

Upvotes: 0

Views: 57

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try simple:

SELECT *, 'FALSE' As is_book
FROM cars c
WHERE NOT EXISTS (
   SELECT * FROM book_cars b
   WHERE b.car_id=c.id AND book = 't'
)

you can also use a subquery:

SELECT *
FROM (
   SELECT cars.*, 
          CASE WHEN EXISTS (
              SELECT book_cars.* FROM book_cars 
              WHERE book_cars.car_id=cars.id AND book='t') 
          THEN 'TRUE' ELSE 'FALSE' END  AS is_book
   FROM cars 
) x
WHERE is_book='FALSE';

EDIT

The problem in your query is in WHERE clause in the last line below:

SELECT cars.*, 
       CASE WHEN EXISTS (
            SELECT book_cars.* FROM book_cars 
            WHERE book_cars.car_id=cars.id AND book='t') 
       THEN 'TRUE' ELSE 'FALSE' END  AS 'is_book' 
FROM cars 
WHERE is_book='FALSE';

In standard SQL WHERE clause doesn't see any expression (or "column") declared in the SELECT clause, it can only see columns from tables in the FROM clause.
is_book column is declared in the SELECT clause, so WHERE cannot see it.
If you use a subquery, like:

SELECT *
FROM ( --- subquery
   SELECT some_expression AS new_column
   FROM ....
) x
WHERE new_column = 111

then the outer query sees that new column.

You can also use HAVING clause:

SELECT cars.*, 
       CASE WHEN EXISTS (
            SELECT book_cars.* FROM book_cars 
            WHERE book_cars.car_id=cars.id AND book='t') 
       THEN 'TRUE' ELSE 'FALSE' END  AS 'is_book' 
FROM cars 
HAVING is_book='FALSE';

because HAVING, in contrast to WHERE, does see columns declared at SELECT level.

Upvotes: 1

Related Questions