Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create extension postgis;

create table business(
  id int generated by default as identity primary key,
  business_name text,
  x numeric,
  y numeric,
  geom geometry(Point,4326) generated always as 
      (st_setsrid(st_makepoint(x,y),4326)) stored,
  geohash text generated always as 
      (st_geohash(st_setsrid(st_makepoint(x,y),4326))) stored);

select setseed(.2);
insert into business (business_name,x,y)
select 'business_'||n,
        x,
        y
from generate_series(1,1e5,1)a(n),
  lateral(select n,random()*180 x,90-random()*180 y)b(n1,x,y);

create index geom_idx on business using gist(geom);

select * from business limit 5;

CREATE EXTENSION



CREATE TABLE




setseed






SELECT 1



ERROR:  could not extend file "base/5/17457": No space left on device
HINT:  Check free disk space.


vacuum analyze business;

ERROR:  relation "business" does not exist


prepare postgis_based as
select id,business_name,x,y,geohash
      ,(select geom from business where id=42)<->geom distance 
from business
order by distance
limit 5;

explain analyze execute postgis_based;
execute postgis_based;

ERROR:  relation "business" does not exist
LINE 4: from business
             ^


create extension pg_trgm ;
create index on business using gist (geohash gist_trgm_ops);

CREATE EXTENSION



ERROR:  relation "business" does not exist


vacuum analyze business;

ERROR:  relation "business" does not exist


prepare geohash_based as
select id,business_name,x,y,geohash
      ,(select geohash from business where id=42)<->geohash distance 
from business
order by distance
limit 5;

explain analyze execute geohash_based;
execute geohash_based;

ERROR:  relation "business" does not exist
LINE 4: from business
             ^


prepare postgis_based2 as
select id,business_name,x,y,geohash
from business
where st_dwithin((select geom from business where id=42),geom,1)
limit 5;

explain analyze execute postgis_based2;
execute postgis_based2;

ERROR:  relation "business" does not exist
LINE 3: from business
             ^


prepare geohash_based2 as
select id,business_name,x,y,geohash
from business
where geohash ^@ (select left(geohash,2) from business where id=42)
limit 5;

explain analyze execute geohash_based2;
execute geohash_based2;

ERROR:  relation "business" does not exist
LINE 3: from business
             ^