Solved

mysql to postgresql

Posted on 2010-09-15
6
614 Views
Last Modified: 2012-08-13
this query is running in mysql but when i tried to run in postgres then i got the below error

ERROR: operator does not exist: character varying * double precision at  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. at  Position: 162

SELECT  geodb.geonames.FULL_NAME,geodb.geonames.SORT_NAME, geodb.geonames.FULL_NAME_ND, ((ACOS(SIN(29.3574144 * PI() / 180) * SIN(geodb.geonames.LAT * PI() / 180) + COS(29.3574144 * PI() / 180) * COS(geodb.geonames.LAT * PI() / 180) * COS((76.9813056 - geodb.geonames.LONGI) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance FROM geodb.geonames, navl.objtable   WHERE geodb.geonames.LONGI > 76.9813056 - '0.08' AND geodb.geonames.LONGI < 76.9813056 + '0.08' AND geodb.geonames.LAT > 29.3574144 - '0.08' AND geodb.geonames.LAT < 29.3574144 + '0.08' ;

Open in new window

0
Comment
Question by:Sachin_Ajmani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Expert Comment

by:mattibutt
ID: 33689322
Every dabtabase slightlu different syntax you might wanna create query according to ansi standard
0
 
LVL 40

Expert Comment

by:lcohan
ID: 33691683
MySQL SQL language is a procedural language more in line with PostgreSQL pl/pgsql.

PL/PgSQL - this is PostgreSQL defacto Procedural Language. It is not always installed by default in a database but the language handler is always available for installation. Do you have it installed in the database where you run your MySQL code?
0
 

Author Comment

by:Sachin_Ajmani
ID: 33700699
As per suggestion installe"d PL/PgSQL - this is PostgreSQL defacto Procedural Languag and run the query in postgres and got the same error message.

[Err] ERROR:  operator does not exist: character varying * double precision
LINE 1: ...3574144 * PI() / 180) * SIN(postgres.geonames.LAT * PI() / 1...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 2

Expert Comment

by:supersepp
ID: 33741777
seems like postgres.geonames.LAT is of type varchar, either alter your column definition or use a type-cast like SIN(postgres.geonames.LAT::decimal * PI())
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 33760743
try cast to float

geonames.LAT::float
0
 

Author Closing Comment

by:Sachin_Ajmani
ID: 34264524
good
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question