Check if a value is within a numrange in PostgreSQL

Question

In PostgreSQL 16, I need to check if a value is within a numrange stored in a table.
I found the following example with an int4range

SELECT 14 <@ (SELECT my_range
FROM my_table
WHERE id = 1);

But when I tried it with my numrange, I got

ERROR:  operator does not exist: integer <@ numrange  
LINE 1: SELECT 14 <@ (SELECT my_range  
                  ^  
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

I'm not sure what I need to cast. How should I proceed?

Answer 1

TLDR

Cast to numeric:

SELECT numeric '14' <@ '[1,2]'::numrange;  -- works

Why?

There is an implicit cast from integer to numeric. That is not the problem.

The problem is that the numeric constant 14 (without single-quotes) initially resolves to type integer, the operator you want is the generic anyelement <@ anyrange, and operator type resolution does not arrive there.

anyelement and anyrange are pseudo types, more specifically, polymorphic types. The operator to be used is determined by actual input types. You must be specific about the input, Postgres cannot assume too much. Furthermore, quoting the manual:

[...] if there are positions declared anyrange and others declared anyelement or anyarray, the actual range type in the anyrange positions must be a range whose subtype is the same type appearing in the anyelement positions and the same as the element type of the anyarray positions.

In short, if one is given, the other must match exactly.

This expression works:

SELECT 14. <@ '[1,2]'::numrange;  -- works, a bit obfuscated

Because 14. is numeric constant (a.k.a. "numeric literal") that is initially presumed to be type numeric. (The word "numeric" in "numeric constant" does not predetermine the type to be numeric, mind you. Same word, two distinct meanings.) Numeric literals resolve to integer, bigint or numeric. The manual:

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits); otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric.

Bold emphasis mine.

There are no "floating point literals" in Postgres, strictly speaking. And trying an explicit floating point (float = float8 = double precision) fails also:

SELECT float '14' <@ '[1,2]'::numrange;  -- fails!

In simple cases, you could still use '14' (with single-quotes) as string constant (a.k.a. "string literal") without explicit cast. Then, operator type resolution would eventually coerce that to the matching type numeric.

SELECT '14' <@ '[1,2]'::numrange;  -- fails in this case!

However, it fails in this particular case, because there is another operator anyrange <@ anyrange in the system, which binds first. The left type is the same as the right, and that binds earlier. So the literal is presumed to be type numrange, and trying to cast it produces this error:

ERROR: Missing left parenthesis or bracket.malformed range literal: "14"

If you'd drop the operator anyrange <@ anyrange, the expression would still fail because there is a third operator 'anymultirange' <@ anyrange, and the ambiguity would result in:

ERROR: operator is not unique: numrange @> unknown

It would work after dropping that, too. (I verified. But, obviously, don't do that!)

So, unless you register an operator for numeric <@ numrange, or even integer <@ numrange (don't!), the remaining solution is an explicit cast. No ambiguity left, the gold standard. (Or work with a variable / parameter typed numeric to begin with.):

SELECT numeric '14' <@ '[1,2]'::numrange;  -- works
SELECT '14'::numeric <@ '[1,2]'::numrange;  -- works

Well, you could cast numrange to int4range. There is no direct cast registered, you could use text as stepping stone:

SELECT 14 <@ '[1,2]'::numrange::text::int4range;  -- works, but ugly

Of course, all range boundaries have to be valid for int4 (then why use numrange to begin with?), and it's a very expensive, non-sargable expression. So, don't, unless circumstances force your hand.

Answer 2

I'm not sure what I need to cast

Since you cannot cast numrange to int4range, obviously the only candidate for casting is the literal. You can do it explicitly:

select 14::numeric <@ (select my_range from ranges where id = 1)

or use a numeric literal that can be implicitly cast to a numeric:

select 14. <@ (select my_range from ranges where id = 1)

Generally though you should make all your cast explicit.

Fiddle.