CODEWITHSUNDEEP
mysqlruby-on-railspostgresqlheroku
DavidLBatey
DavidLBatey

Reputation: 322

Postgresql error (heroku) - works locally on mysql

It's a zip code search that's working fine in development using mysql but when deployed to heroku it's blowing up.

ActiveRecord::StatementInvalid (PG::Error: ERROR:  syntax error at end of input
LINE 9:                             <= 6.21371192)
                                                  ^
: SELECT "zip_codes"."zip_code" FROM "zip_codes"  WHERE ((latitude BETWEEN 37.68949912779737
                        AND 37.869158872202625)
                        AND (longitude BETWEEN -122.53280459311426
                        AND -122.30549540688574)
                        AND sqrt(pow(69.172 *
                            (latitude-37.779329),2) +
                            pow(54.67189446319151 *
                            (longitude--122.41915),2))
                            <= 6.21371192)

Upvotes: 0

Views: 93

Answers (2)

DavidLBatey
DavidLBatey

Reputation: 322

It was the double negative that was causing issues with postgres. Placing the co-ordinates in brackets has fixed the problem.

SELECT "zip_codes"."zip_code" 
FROM "zip_codes"
WHERE ((latitude BETWEEN 37.68949912779737
        AND 37.869158872202625)
        AND (longitude BETWEEN -122.53280459311426
        AND -122.30549540688574)
        AND sqrt(pow(69.172 *
            (latitude - (37.779329)),2) +
             pow(54.67189446319151 *
             (longitude - (-122.41915)),2))
             <= 6.21371192)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think you have an extra closing paren at the end. Here is the code formatted so closing parens are generally aligned with the opening paren:

SELECT "zip_codes"."zip_code"
FROM "zip_codes" 
WHERE ((latitude BETWEEN 37.68949912779737 AND 37.869158872202625
       ) AND
       (longitude BETWEEN -122.53280459311426 AND -122.30549540688574
       ) AND
       sqrt(pow(69.172 * (latitude-37.779329),2
               ) +
            pow(54.67189446319151 * (longitude--122.41915),2
               )
           ) <= 6.21371192
      ))
-------^ this one is extra.  Get rid of it.

I do not know why MySQL would process this but Postgres would not, however. Perhaps the paren was added accidentally when switching servers.

Upvotes: 0

Related Questions