Re: [HACKERS] WIP: RangeTypes

2011-02-08 Thread Jeff Davis
On Mon, 2011-02-07 at 20:32 +0200, Peter Eisentraut wrote: > Have you considered a grammar approach like for arrays, so that you > would write something like > > CREATE TABLE ... ( > foo RANGE OF int > ); > > instead of explicitly creating a range type for every scalar type in > existence? I

Re: [HACKERS] WIP: RangeTypes

2011-02-07 Thread Peter Eisentraut
On sön, 2011-01-30 at 14:52 -0800, Jeff Davis wrote: > * naming issues: > - period -> tsrange ? > - periodtz -> tstzrange ? > - intrange -> int4range Have you considered a grammar approach like for arrays, so that you would write something like CREATE TABLE ... ( foo RANGE OF in

Re: [HACKERS] WIP: RangeTypes

2011-02-02 Thread Jeff Davis
On Sun, 2011-01-30 at 17:14 -0500, Tom Lane wrote: > Jeff Davis writes: > > On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: > >> postgres=# select '[18,20]'::numrange @> 19; > >> ERROR: operator does not exist: numrange @> integer > >> LINE 1: select '[18,20]'::numrange @> 19; > >> ^ > >> HI

Re: [HACKERS] WIP: RangeTypes

2011-01-30 Thread Jeff Davis
Another updated patch. Improvements: * Full GiST support - Thanks to Alexander Korotkov for sending me a new picksplit algorithm for my "temporal" project on pgfoundry. I modified it for use with range types, including a (hopefully) intelligent way of handling empty and unbounded ranges.

Re: [HACKERS] WIP: RangeTypes

2011-01-30 Thread Tom Lane
Jeff Davis writes: > On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: >> postgres=# select '[18,20]'::numrange @> 19; >> ERROR: operator does not exist: numrange @> integer >> LINE 1: select '[18,20]'::numrange @> 19; >> ^ >> HINT: No operator matches the given name and argument type(s). You

Re: [HACKERS] WIP: RangeTypes

2011-01-30 Thread Thom Brown
On 30 January 2011 21:33, Jeff Davis wrote: > [ trying a third time to send this message, apparently there were > infrastructure problems before ] > > On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: >> postgres=# select '[18,20]'::numrange @> 19; >> ERROR:  operator does not exist: numrange @

Re: [HACKERS] WIP: RangeTypes

2011-01-30 Thread Jeff Davis
[ trying a third time to send this message, apparently there were infrastructure problems before ] On Sun, 2011-01-30 at 02:55 +, Thom Brown wrote: > postgres=# select '[18,20]'::numrange @> 19; > ERROR: operator does not exist: numrange @> integer > LINE 1: select '[18,20]'::numrange @> 19;

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Thom Brown
On 30 January 2011 02:55, Thom Brown wrote: > On 29 January 2011 19:53, Jeff Davis wrote: >> On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: >>> Jeff Davis writes: >>> > On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: >>> > Also, if I try the same, but with a different name for the type,

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Thom Brown
On 29 January 2011 19:53, Jeff Davis wrote: > On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: >> Jeff Davis writes: >> > On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: >> > Also, if I try the same, but with a different name for the type, I get >> > the same error.  Why does that restrict

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Jeff Davis
On Sat, 2011-01-29 at 14:42 -0500, Tom Lane wrote: > Jeff Davis writes: > > On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: > > Also, if I try the same, but with a different name for the type, I get > > the same error. Why does that restriction exist? Can't you have > > types which happen t

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Tom Lane
Jeff Davis writes: > On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: > Also, if I try the same, but with a different name for the type, I get > the same error. Why does that restriction exist? Can't you have > types which happen to use the exact same subtype? > At first, that's how I desig

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Jeff Davis
On Sat, 2011-01-29 at 11:00 -0800, David E. Wheeler wrote: > I think I'm just revealing my ignorance of these index types and what > they're good for. My impression has been that GIN was a better but > less-full-featured alternative to GiST and getting better with Tom's > recent fixes for its handl

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Jeff Davis
On Fri, 2011-01-28 at 14:15 -0500, Chris Browne wrote: > Mind you, timestamptzrange seems a mite *long* to me. Right. I think we might need to compromise here an use some shorter names. tsrange/tstzrange/numrange seem reasonable to me. > Making sure it's consistent with int4, int8, bigint sure se

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread David E. Wheeler
On Jan 29, 2011, at 10:57 AM, Jeff Davis wrote: > On Fri, 2011-01-28 at 10:41 -0800, David E. Wheeler wrote: >> +1 in principal. I think we should try to avoid the user of the term >> "period" if possible, and I see definite benefits to a simple model of >> $typename . 'range'; > > Interesting, I

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Thom Brown
On 29 January 2011 18:52, Jeff Davis wrote: > On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: >> Also, how do you remove a range type which coincides with a system >> range type.  For example: >> >> postgres=#  CREATE TYPE numrange AS RANGE (SUBTYPE=interval, >>    SUBTYPE_CMP=interval_cmp);

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Jeff Davis
On Fri, 2011-01-28 at 10:41 -0800, David E. Wheeler wrote: > +1 in principal. I think we should try to avoid the user of the term > "period" if possible, and I see definite benefits to a simple model of > $typename . 'range'; Interesting, I didn't realize that PERIOD was such an undesirable type n

Re: [HACKERS] WIP: RangeTypes

2011-01-29 Thread Jeff Davis
On Fri, 2011-01-28 at 21:52 +, Thom Brown wrote: > > This is not very graceful: > > > > postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=numeric, > > SUBTYPE_CMP=numeric_cmp); > > ERROR: duplicate key value violates unique constraint > > "pg_range_rgnsubtype_index" > > DETAIL: Key (rngsub

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread Thom Brown
On 28 January 2011 20:28, Thom Brown wrote: > On 28 January 2011 07:45, Jeff Davis wrote: >> Updated patch. >> >> Changes: >> >>  * Documentation for operators/functions >>  * a comprehensive set of operators and functions >>  * BTree opclass >>  * Hash opclass >>  * built-in range types: >>    -

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread Thom Brown
On 28 January 2011 07:45, Jeff Davis wrote: > Updated patch. > > Changes: > >  * Documentation for operators/functions >  * a comprehensive set of operators and functions >  * BTree opclass >  * Hash opclass >  * built-in range types: >    - PERIOD (timestamp) >    - PERIODTZ (timestamptz) >    -

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread Chris Browne
pg...@j-davis.com (Jeff Davis) writes: > On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: >> For consistency, and in order not to continue our atrocious naming >> tradition, I'd like to propose that the above be named timestamprange >> (tsrange for short) and timestamptzrange (tstzrange for

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread David E. Wheeler
On Jan 28, 2011, at 9:48 AM, Jeff Davis wrote: > On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: >> For consistency, and in order not to continue our atrocious naming >> tradition, I'd like to propose that the above be named timestamprange >> (tsrange for short) and timestamptzrange (tstzra

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread Tom Lane
Jeff Davis writes: > On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: >> For consistency, and in order not to continue our atrocious naming >> tradition, I'd like to propose that the above be named timestamprange >> (tsrange for short) and timestamptzrange (tstzrange for short). > No real o

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread Jeff Davis
On Fri, 2011-01-28 at 09:17 -0800, David Fetter wrote: > For consistency, and in order not to continue our atrocious naming > tradition, I'd like to propose that the above be named timestamprange > (tsrange for short) and timestamptzrange (tstzrange for short). No real objection, but I'd like to s

Re: [HACKERS] WIP: RangeTypes

2011-01-28 Thread David Fetter
On Thu, Jan 27, 2011 at 11:45:30PM -0800, Jeff Davis wrote: > Updated patch. > > Changes: > > * Documentation for operators/functions > * a comprehensive set of operators and functions > * BTree opclass Yay! > * Hash opclass > * built-in range types: > - PERIOD (timestamp) > -

Re: [HACKERS] WIP: RangeTypes

2011-01-27 Thread Jeff Davis
Updated patch. Changes: * Documentation for operators/functions * a comprehensive set of operators and functions * BTree opclass * Hash opclass * built-in range types: - PERIOD (timestamp) - PERIODTZ (timestamptz) - DATERANGE (date) - INTRANGE (int4) - NUMRANGE (nume

Re: [HACKERS] WIP: RangeTypes

2011-01-21 Thread Robert Haas
On Fri, Jan 21, 2011 at 2:30 PM, Jeff Davis wrote: > On Fri, 2011-01-21 at 12:31 -0500, Robert Haas wrote: >> On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis wrote: >> > New patch. I added a lot of generic range functions, and a lot of >> > operators. >> > >> > There is still more work to do, this is

Re: [HACKERS] WIP: RangeTypes

2011-01-21 Thread Jeff Davis
On Fri, 2011-01-21 at 12:31 -0500, Robert Haas wrote: > On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis wrote: > > New patch. I added a lot of generic range functions, and a lot of > > operators. > > > > There is still more work to do, this is just an updated patch. The > > latest can be seen on the g

Re: [HACKERS] WIP: RangeTypes

2011-01-21 Thread Robert Haas
On Thu, Jan 20, 2011 at 4:29 AM, Jeff Davis wrote: > New patch. I added a lot of generic range functions, and a lot of > operators. > > There is still more work to do, this is just an updated patch. The > latest can be seen on the git repository, as well: So is this 9.2 material at this point? -

Re: [HACKERS] WIP: RangeTypes

2011-01-20 Thread Jeff Davis
New patch. I added a lot of generic range functions, and a lot of operators. There is still more work to do, this is just an updated patch. The latest can be seen on the git repository, as well: http://git.postgresql.org/gitweb?p=users/jdavis/postgres.git;a=log;h=refs/heads/rangetypes Regards,

Re: [HACKERS] WIP: RangeTypes

2011-01-17 Thread Josh Berkus
On 1/17/11 1:09 PM, Jeff Davis wrote: > I feel like I'm making this too complicated. Should I just scope out > NULL range boundaries for the first cut, and leave room in the > representation so that it can be added when there is a more thorough > proposal for NULL range boundaries? Well, NULL rang

Re: [HACKERS] WIP: RangeTypes

2011-01-17 Thread Tom Lane
Jeff Davis writes: > I feel like I'm making this too complicated. Should I just scope out > NULL range boundaries for the first cut, and leave room in the > representation so that it can be added when there is a more thorough > proposal for NULL range boundaries? +1. I'm far from convinced that

Re: [HACKERS] WIP: RangeTypes

2011-01-17 Thread David Fetter
On Mon, Jan 17, 2011 at 01:09:26PM -0800, Jeff Davis wrote: > When defining generic range functions, there is quite a bit of extra > complexity needed to handle special cases. > > The special cases are due to: > * empty ranges > * ranges with infinite boundaries > * ranges with NULL boundaries

Re: [HACKERS] WIP: RangeTypes

2011-01-17 Thread Jeff Davis
When defining generic range functions, there is quite a bit of extra complexity needed to handle special cases. The special cases are due to: * empty ranges * ranges with infinite boundaries * ranges with NULL boundaries * ranges with exclusive bounds (e.g. "(" or ")"). Infinite bounds, and e

Re: [HACKERS] WIP: RangeTypes

2011-01-14 Thread Jeff Davis
Updated patch. Summary of changes: * More generic functions * pg_dump support * remove typmod support until it can be done correctly * added some tests There is still quite a bit left, including (numbers match up with previous TODO list): 1. Generic functions -- still more work to

Re: [HACKERS] WIP: RangeTypes

2011-01-12 Thread Jeff Davis
On Tue, 2011-01-11 at 11:13 -0800, David Fetter wrote: > > 3. Typmod -- There is still one annoyance about typmod remaining. I need > > to treat it like an array in find_typmod_coercion_function(), and then > > create a coercion expression. Is it worth it? Would typmod on a range be > > confusing,

Re: [HACKERS] WIP: RangeTypes

2011-01-11 Thread David Fetter
On Tue, Jan 11, 2011 at 01:16:47AM -0800, Jeff Davis wrote: > Ok, I have made some progress. This is still a proof-of-concept patch, > but the important pieces are working together. > > Synopsis: > > CREATE TYPE numrange AS RANGE (SUBTYPE=numeric, > SUBTYPE_CMP=numeric_cmp); > > SELECT