Thread: What are the characteristics of a good user-defined data type?
List,
This is a general question for my own education. Something I read earlier today triggered some ideas in my head, and out of curiosity I’m researching and learning.
What characteristics of data sets lend themselves to reasonable user-defined data types? Are there characteristics of a data type that would limit its usefulness?
I’ve reviewed the PostgreSQL documentation on user defined data types. It seems that data types that can’t be ordered or compared for equality would be bad candidates. After all, if a data type can’t be indexed or used in a where clause, what value does a custom type bring over a binary or textual representation?
Additionally, the careful tone in the documentation regarding the definition of the comparison and equality operators suggests that these definitions may be an exceptionally delicate matter. Any experience or suggestions on the matter?
Tim Hart
"Tim Hart" <tjhart@mac.com> writes:
> I've reviewed the PostgreSQL documentation on user defined data types. It
> seems that data types that can't be ordered or compared for equality would
> be bad candidates. After all, if a data type can't be indexed or used in a
> where clause, what value does a custom type bring over a binary or textual
> representation?
Well, the possibility of error-checking for bad values might alone
justify a custom type, depending on what you're doing. A type with no
support beyond the required I/O functions could offer that.
But it's kinda hard to imagine a datatype in which there is no
meaningful way to define equality ...
regards, tom lane
Could custom types benefit significantly from custom operators as well? Do
custom C functions stand a good chance of introducing speed benefits over
their raw SQL or pl/sql counterparts? Or is the field too broad to speculate
on the general case?
The scenario that inspired this question was about data that had to be
stored accurately, but the data itself wasn't usually precise. You could
think of an individual datum being a set of ranges. You could definitely
define equality on this data type, but the ordering operators would probably
be meaningless.
On the other hand, some (but not all) of the geometric operators could
probably be interpreted to apply to this data set, as long as you ignore the
'above' and 'below' semantics, and replace the concepts of left and right
with less than and greater than. So for example, while
<< (is strictly left of)
Wouldn't make sense, using the same operator to mean 'strictly less than'
might.
Would R-tree indexes be useful for a data type like this? Would it be
possible to define the base type such that an R-tree index would always be
created?
Once again - this is entirely idle curiosity. This isn't anything I have a
real need for.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, June 07, 2006 9:37 AM
To: Tim Hart
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] What are the characteristics of a good user-defined
data type?
"Tim Hart" <tjhart@mac.com> writes:
> I've reviewed the PostgreSQL documentation on user defined data types. It
> seems that data types that can't be ordered or compared for equality would
> be bad candidates. After all, if a data type can't be indexed or used in a
> where clause, what value does a custom type bring over a binary or textual
> representation?
Well, the possibility of error-checking for bad values might alone
justify a custom type, depending on what you're doing. A type with no
support beyond the required I/O functions could offer that.
But it's kinda hard to imagine a datatype in which there is no
meaningful way to define equality ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On Wed, Jun 07, 2006 at 12:57:15PM -0500, Tim Hart wrote: > Could custom types benefit significantly from custom operators as > well? Yes. > Do custom C functions stand a good chance of introducing speed > benefits over their raw SQL or pl/sql counterparts? Or is the field > too broad to speculate on the general case? Generally, it's too broad to say. Note also that programmer time is a valuable resource and CPU time is cheap. > The scenario that inspired this question was about data that had to > be stored accurately, but the data itself wasn't usually precise. > You could think of an individual datum being a set of ranges. You > could definitely define equality on this data type, but the ordering > operators would probably be meaningless. Right. Just don't define a < or > operator, but do define an = operator on the type :) > On the other hand, some (but not all) of the geometric operators could > probably be interpreted to apply to this data set, as long as you ignore the > 'above' and 'below' semantics, and replace the concepts of left and right > with less than and greater than. So for example, while > > << (is strictly left of) > > Wouldn't make sense, using the same operator to mean 'strictly less than' > might. > > Would R-tree indexes be useful for a data type like this? Would it > be possible to define the base type such that an R-tree index would > always be created? Kinda depends on what you're doing. > Once again - this is entirely idle curiosity. This isn't anything I > have a real need for. You might some day. One of PostgreSQL's Killer Features(TM) is its radical extensibility. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!