Solved

mysql to postgresql

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
showing numeric numbers 2 38
Trigger usage 2 75
when to use sequences in mysql 4 33
Incorrect definition of table mysql.proc 7 50
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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.

860 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