Skip site navigation (1) Skip section navigation (2)

Re: WIP: RangeTypes

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Thom Brown <thom(at)linux(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: RangeTypes
Date: 2011-01-29 18:52:09
Message-ID: 1296327129.11513.504.camel@jdavis (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 2011-01-28 at 21:52 +0000, 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 (rngsubtype)=(1700) already exists.

You're right, that should be a much nicer error message.

> > 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 designed it. Then, I realized that the type
system needs to know the range type from the element type in order for
something like ANYRANGE to work.

There's a workaround though: create a domain over numeric, and then
create a range over mynumeric.

=# create domain mynumeric as numeric;
CREATE DOMAIN
=# create type numrange2 as range (subtype=numeric,
subtype_cmp=numeric_cmp);
ERROR:  duplicate key value violates unique constraint
"pg_range_rgnsubtype_index"
DETAIL:  Key (rngsubtype)=(1700) already exists.
=# create type numrange2 as range (subtype=mynumeric,
subtype_cmp=numeric_cmp);
CREATE TYPE
=# select range(1.1::mynumeric,2.2::mynumeric);
    range     
--------------
 [ 1.1, 2.2 )
(1 row)


> 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);
> CREATE TYPE
> postgres=# drop type numrange;
> ERROR:  cannot drop type numrange because it is required by the database system
> 
> Is this because I shouldn't have been able to create this type in the
> first place?

The types are in two different schemas. It's just as though you created
a table called pg_class.

To drop the one you created, do:
  DROP TYPE public.numrange;

Regards,
	Jeff Davis



In response to

Responses

pgsql-hackers by date

Next:From: Thom BrownDate: 2011-01-29 18:57:33
Subject: Re: WIP: RangeTypes
Previous:From: Pavel StehuleDate: 2011-01-29 18:39:54
Subject: SPI_exec doesn't return proc context (on 9.1)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group