Is there a postgres CLOSEST operator?

Question

I'm looking for something that, given a table like:

| id | number | |  1 |     .7 | |  2 |   1.25 | |  3 |   1.01 | |  4 |    3.0 | 

the query SELECT * FROM my_table WHEREnumberCLOSEST(1) would return row 3. I only care about numbers. Right now I've got a procedure that just loops over every row and does a comparison, but I figure the information should be available from a b-tree index, so this might be possible as a builtin, but I can't find any documentation suggesting that it does.

Answer 1

I may be a little off on the syntax, but this parameterized query (all the ? take the '1' of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed].

SELECT * FROM (   (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above   UNION ALL   (SELECT id, number FROM t WHERE number < ? ORDER BY number DESC LIMIT 1) as below )  ORDER BY abs(?-number) LIMIT 1; 

The query plan for this with a table of ~5e5 rows (with an index on number) looks like this:

psql => explain select * from (         (SELECT id, number FROM t WHERE number >= 1 order by number limit 1)          union all         (select id, number from t where number < 1 order by number desc limit 1) ) as make_postgresql_happy  order by abs (1 - number)  limit 1;                                                   QUERY PLAN --------------------------------------------------------------------------------------------------------------  Limit  (cost=0.24..0.24 rows=1 width=12)    ->  Sort  (cost=0.24..0.24 rows=2 width=12)          Sort Key: (abs((1::double precision - public.t.number)))          ->  Result  (cost=0.00..0.23 rows=2 width=12)                ->  Append  (cost=0.00..0.22 rows=2 width=12)                      ->  Limit  (cost=0.00..0.06 rows=1 width=12)                            ->  Index Scan using idx_t on t  (cost=0.00..15046.74 rows=255683 width=12)                                  Index Cond: (number >= 1::double precision)                      ->  Limit  (cost=0.00..0.14 rows=1 width=12)                            ->  Index Scan Backward using idx_t on t  (cost=0.00..9053.67 rows=66136 width=12)                                  Index Cond: (number < 1::double precision) (11 rows)