FAQ

Craig Ringer wrote:
showing that your issue isn't actually with DISTINCT at all, but with Pg's unwillingness to *implicitly* cast a value of explict text type to another type.
Is there a way to make values of "undefined" type pass through the SELECT DISTINCT filter (getting checked for uniqueness) but remain "undefined" if all the values supplied for the column are "undefined"? I don't know if the internal design of SELECT DISTINCT and the type system would allow for this, but if it would, it would take care of Farid's problem without introducing implicit type casts.

Search Discussions

  • Robert Haas at Jun 7, 2010 at 2:52 pm

    On Mon, Jun 7, 2010 at 10:30 AM, Andy Balholm wrote:
    Craig Ringer wrote:
    showing that your issue isn't actually with DISTINCT at all, but with Pg's unwillingness to *implicitly* cast a value of explict text type to another type.
    Is there a way to make values of "undefined" type pass through the SELECT DISTINCT filter (getting checked for uniqueness) but remain "undefined" if all the values supplied for the column are "undefined"? I don't know if the internal design of SELECT DISTINCT and the type system would allow for this, but if it would, it would take care of Farid's problem without introducing implicit type casts.
    The issue isn't what's technically possible, but what's least likely
    to lead to surprising behavior. This whole thread is basically about
    whether implicit casts to and from text are a good idea or not. The
    OP obviously thinks they are, and everyone else (whether they agree
    with the behavior or not) is trying to explain the reasons why we
    don't have them.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Greg Stark at Jun 7, 2010 at 4:32 pm

    On Mon, Jun 7, 2010 at 3:52 PM, Robert Haas wrote:
    Is there a way to make values of "undefined" type pass through the SELECT DISTINCT filter (getting checked for uniqueness) but remain "undefined" if all the values supplied for the column are "undefined"?
    The concept of "uniqueness" depends on what data type the data is
    interpreted as.

    --
    greg
  • Tom Lane at Jun 7, 2010 at 2:53 pm

    Andy Balholm writes:
    Is there a way to make values of "undefined" type pass through the
    SELECT DISTINCT filter (getting checked for uniqueness) but remain
    "undefined"
    No. What is your criterion for deciding that two values are distinct?
    It's not possible to do that without imputing a data type to them.
    (In particular, comparing the character strings amounts to deciding
    that they are of a textual data type --- failing to acknowledge that
    isn't a workaround but merely sloppy thinking.)

    regards, tom lane
  • Andy Balholm at Jun 7, 2010 at 3:16 pm

    On Jun 7, 2010, at 7:53 AM, Tom Lane wrote:

    Andy Balholm <andy@balholm.com> writes:
    Is there a way to make values of "undefined" type pass through the
    SELECT DISTINCT filter (getting checked for uniqueness) but remain
    "undefined"
    No. What is your criterion for deciding that two values are distinct?
    It's not possible to do that without imputing a data type to them.
    (In particular, comparing the character strings amounts to deciding
    that they are of a textual data type --- failing to acknowledge that
    isn't a workaround but merely sloppy thinking.)

    regards, tom lane
    I see your point about the fuzziness of deciding what constitutes a distinct value before the type is determined. My proposal was so general that it would still open a can of worms.

    In Farid's particular use case, it's easy to see that the values aren't distinct, because they're all textually identical. In that case, SELECT DISTINCT could simply ignore that column while deciding which rows are distinct, and then tack it back on when it returns its result. That would be a special-case hack, but I suspect that it would actually cover most cases where undefined types are used in SELECT DISTINCT, since undefined types come from literals in the SQL, which would generally be the same for all rows. Data that varies by row would usually come from real tables, where types are already defined.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJun 7, '10 at 2:31p
activeJun 7, '10 at 4:32p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase