Solved

mysql to postgresql

Posted on 2010-09-15
6
611 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

737 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