Feature request: Range types from Postgresql #55

Activity

1. agershun commented on Feb 4, 2015

   

   agershun

   on Feb 4, 2015

   Member

   More actions

   Thank you for request! May I ask you to answer on the following questions:

   1. Is it important to keep syntax of operations, like:

       SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); 
       SELECT int4range(10, 20) * int4range(15, 25);

   Now Alasql expression compiler does not check operand types and delegates this work to JavaScript, so I need to create special range_*() functions for these operations, like:

       SELECT range_and(numrange(11.1, 22.2), numrange(20.0, 30.0)); 
       SELECT range_intersect(int4range(10, 20) * int4range(15, 25));

   or create special range operators (like @>):

       SELECT numrange(11.1, 22.2) @&& numrange(20.0, 30.0); 
       SELECT int4range(10, 20) @* int4range(15, 25);

   Which option is better on your opinion?

   1. Postgres has 6 range datatypes

       int4range — Range of integer
       int8range — Range of bigint
       numrange — Range of numeric
       tsrange — Range of timestamp without time zone
       tstzrange — Range of timestamp with time zone
       daterange — Range of date
   

   But for Alasql all of them will be realized with only 'typeless' range. Do I need to keep all these 6 types or can use only one, like: RANGE(1,2)?

   1. Should I implement '::' as an additional form to CAST/CONVERT functions?

   Thank you!
2. agershun

   added

   ! Feature request

   on Feb 4, 2015
3. agershun

   self-assigned this

   on Feb 4, 2015
4. PhilAndrew commented on Feb 4, 2015

   

   PhilAndrew

   on Feb 4, 2015

   Author

   More actions

   Thanks for the fast response! I suggest:

   SELECT range_and(numrange(11.1, 22.2), numrange(20.0, 30.0)); 
   SELECT range_intersect(int4range(10, 20) * int4range(15, 25));
   

   Is declarative, functional and readable, so I like it over special operators.

   Typeless ranges are fine.

   My usage is for a school booking system, I try to keep all the data in the client side and store the database on S3 file storage rather than having a central database, this can keep the cost low. Since the cost and speed of sending 1mb of data to the browser is relatively low now, sending the entire database to the browser is cheap, which was not the case in the past.

   The typical usage is trying to find if some date and times have free available timeslots, for a date range like 13th to 16th of October during the times of 9am to 12pm and 3pm to 6pm, try to find what items exist in the database which also match other criteria. Then many other types of queries relates to ranges and dates.

   I'm surprised to get such a good response! Thought, I wonder how efficient this would be.
5. agershun

   added

   Dialect: Postgres

   on May 20, 2015
6. agershun

   added this to the SQLThis milestone has been deleted milestone on May 21, 2015
7. mathiasrw commented on Jul 14, 2015

   

   mathiasrw

   on Jul 14, 2015

   Member

   More actions

   I have added this as a featurerequest: http://feathub.com/agershun/alasql/+22
8. mathiasrw

   assigned

   Copilot

   and unassigned

   agershun

   on Dec 7, 2025
9. mathiasrw

   added

   Theme: SQL compliance

   on Oct 21, 2026
10. mathiasrw

    modified the milestones: SQL compliance, v5.x Focus, v5.0.0 on Oct 22, 2026