Is there a postgres CLOSEST operator?

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)

You could try something like this:

select *from my_tablewhere abs(1 - number) = (select min(abs(1 - number)) from t)

This isn't that much different than manually looping through the table but at least it lets the database do the looping inside "database space" rather than having to jump back and forth between your function and the database internals. Also, pushing it all into a single query lets the query engine know what you're trying to do and then it can try to do it in a sensible way.

The 2nd answer is correct, but I encountered error on "UNION ALL":

DBD::Pg::st execute failed: ERROR: syntax error at or near "UNION"

I fixed it with this code:

SELECT * FROM  (    (SELECT * FROM table WHERE num >= ? ORDER BY num LIMIT 1)        UNION ALL    (SELECT * FROM table WHERE num < ?  ORDER BY num DESC LIMIT 1)  ) as fooORDER BY abs(?-num) LIMIT 1;

the trick is to remove the AS from the inner tables and use it only on the UNION.