How to implement postresql ranges

[DanielAndreasen]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional, Tuple

from sqlalchemy.dialects.postgresql import NUMRANGE
from sqlmodel import Column, Field, SQLModel


class Example(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    required_field: bool = Field(default=False)
    window: Tuple[float, float] = Field(default=None, sa_column=Column(NUMRANGE))


ex = Example(name="Test", window=(1, 2))
print(1.5 in ex.window)  # Returns False, since my range is just a tuple

Description

My underlying postgresql database contain a NumericRange, which I would of cause like to use. If the range is [1;2] then 1 in ex.window should return True (inclusive bounds), and so should 1.23 in ex.window, but 2.001 in ex.window should return False.
There are other cases like [2;) which means from 2 (inclusive) with no upper limit, etc.

Hod do I go about implementing this?

Operating System

Linux

Operating System Details

Up to date debian

SQLModel Version

0.0.16

Python Version

Python 3.12.0

Additional Context

No response

[joachimhuet]

Well for that you will need to create your own class NumRange and customize it for your usage.
Here is a quick example of what you could do:

class NumRange:
    def __init__(self, lower: Optional[int] = None, upper: Optional[int] = None, bounds: str = '[]'):
        assert not (lower is None and upper is None), 'Both lower and upper bounds cannot be None'
        assert bounds in ['[]', '()', '(]', '[)'], 'Invalid bounds'

        self.lower = lower
        self.upper = upper
        self.bounds = bounds

    def __repr__(self):
        return f'NumRange({self.lower}, {self.upper}, {self.bounds})'

    def __str__(self):
        return f'{self.bounds[0]}{self.lower if self.lower else ""};{self.upper if self.upper else ""}{self.bounds[1]}'

    def __eq__(self, other):
        return isinstance(other, NumRange) and \
               self.lower == other.lower and \
               self.upper == other.upper and \
               self.bounds == other.bounds

    def __contains__(self, value):
        if self.lower is None:
            if self.bounds[1] == ']':
                return value <= self.upper
            if self.bounds[1] == ')':
                return value < self.upper
        if self.upper is None:
            if self.bounds[0] == '[':
                return self.lower <= value
            if self.bounds[0] == '(':
                return self.lower < value
        if self.bounds[0] == '[' and self.bounds[1] == ']':
            return self.lower <= value <= self.upper
        if self.bounds[0] == '[' and self.bounds[1] == ')':
            return self.lower <= value < self.upper
        if self.bounds[0] == '(' and self.bounds[1] == ']':
            return self.lower < value <= self.upper
        if self.bounds[0] == '(' and self.bounds[1] == ')':
            return self.lower < value < self.upper
        return False

# ex:
myrange = NumRange(1, None, "[)")
0.5 in myrange # False
1 in myrange  # True
15324 in myrange # True

myrange = NumRange(None, 1, "[)")
0.5 in myrange # True
1 in myrange  # False
-123456 in myrange # True

myrange = NumRange(1, 10, "[)")
0.5 in myrange # False
1 in myrange  # True
5 in myrange  # True
10 in myrange # False

[DanielAndreasen]

I was wondering if something like this was the way. Thanks for the reply.