[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

mysql to postgresql

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
Sachin_Ajmani
Asked:
Sachin_Ajmani
1 Solution
 
mattibuttCommented:
Every dabtabase slightlu different syntax you might wanna create query according to ansi standard
0
 
lcohanDatabase AnalystCommented:
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
 
Sachin_AjmaniAuthor Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
superseppCommented:
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
 
earth man2Commented:
try cast to float

geonames.LAT::float
0
 
Sachin_AjmaniAuthor Commented:
good
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now