Postgresql error (heroku) - works locally on mysql

Question

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)

You may already know this, but my experience with these kinds of issues was greatly alleviated by developing against Postgres locally — Dhaulagiri
– Dhaulagiri, Commented Aug 5, 2013 at 20:42

rony36 · Accepted Answer · 2013-08-13 06:57:44Z

1

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)

edited Aug 13, 2013 at 6:57

rony36

3,3691 gold badge34 silver badges44 bronze badges

answered Aug 4, 2013 at 20:32

DavidLBatey

3223 silver badges10 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2013-08-04 14:28:43Z

0

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.

answered Aug 4, 2013 at 14:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

DavidLBatey Over a year ago

Sorry, that's a typo on my end. The additional closing bracket isn't there.

Collectives™ on Stack Overflow

Postgresql error (heroku) - works locally on mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related