Solved

mysql to postgresql

Posted on 2010-09-15
6
606 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
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 39

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now