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?
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!