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 type numrange2 as range (subtype=numeric,
ERROR: duplicate key value violates unique constraint
DETAIL: Key (rngsubtype)=(1700) already exists.
=# create type numrange2 as range (subtype=mynumeric,
=# select range(1.1::mynumeric,2.2::mynumeric);
[ 1.1, 2.2 )
> 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,
> 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;
In response to
pgsql-hackers by date
|Next:||From: Thom Brown||Date: 2011-01-29 18:57:33|
|Subject: Re: WIP: RangeTypes|
|Previous:||From: Pavel Stehule||Date: 2011-01-29 18:39:54|
|Subject: SPI_exec doesn't return proc context (on 9.1)|