CODEWITHSUNDEEP
sqlpostgresqlgeolocation
Aaron Harker
Aaron Harker

Reputation: 323

ERROR: invalid input syntax for type numeric: "distance"

I really don't understand what is going on here. I have a table with the following definition.

CREATE TABLE IF NOT EXISTS public.premises
(
    id bigint NOT NULL DEFAULT nextval('premises_id_seq'::regclass),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    address character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default",
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    deleted_at timestamp(0) without time zone,
    city_id bigint,
    owner_id bigint,
    rating numeric(2,1),
    referrer_id bigint,
    latitude numeric(8,5),
    longitude numeric(8,5),
    completed_reviews integer NOT NULL DEFAULT 0,
    slug character varying(500) COLLATE pg_catalog."default",
    phone bigint,
    discount integer,
    available_to smallint,
    max_guest integer,
    CONSTRAINT premises_pkey PRIMARY KEY (id),
    CONSTRAINT city_fk_7421789 FOREIGN KEY (city_id)
        REFERENCES public.locations (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT owner_fk_7200964 FOREIGN KEY (owner_id)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

if I do a select with the following

SELECT premises.id, (
  6371
    *acos(cos(radians(13.75))
    *cos(radians(latitude))
    *cos(radians(longitude)
    -radians(100.5))
    +sin(radians(13.75))
    *sin(radians(latitude)))
  )::NUMERIC AS distance
FROM premises

I get a list of ids and double precision numbers as distance, as expected. But when I try to reduce the number of results with a where clause I get the error.

SELECT premises.id, (
  6371
    *acos(cos(radians(13.75))
    *cos(radians(latitude))
    *cos(radians(longitude)
    -radians(100.5))
    +sin(radians(13.75))
    *sin(radians(latitude)))
  )::NUMERIC AS distance
FROM premises
WHERE 'distance' < 50

Upvotes: 0

Views: 389

Answers (1)

user330315
user330315

Reputation:

There are two errors in your code:

first (and the reason for the error at hand): 'distance' is a string constant, not the reference to a column. So it should be WHERE distance < 50.

But that would result in the next error, as you cannot reference a column alias on the same level where it's introduced. You need to wrap the query into a derived table to do that:

select id, distance
from (
  SELECT premises.id, 
         (6371
          *acos(cos(radians(13.75))
          *cos(radians(latitude))
          *cos(radians(longitude)
          -radians(100.5))
          +sin(radians(13.75))
          *sin(radians(latitude)))
          )::NUMERIC AS distance
  FROM premises
)
WHERE distance < 50

Upvotes: 2

Related Questions