Обсуждение: Re: BUG #5490: INSERT doesn't force cast from text to timestamp

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 anothe=
r type.

Is there a way to make values of "undefined" type pass through the SELECT D=
ISTINCT filter (getting checked for uniqueness) but remain "undefined" if a=
ll 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 introducin=
g implicit type casts.

On Mon, Jun 7, 2010 at 10:30 AM, Andy Balholm <andy@balholm.com> 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 anoth=
er 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 th=
e internal design of SELECT DISTINCT and the type system would allow for th=
is, but if it would, it would take care of Farid's problem without introduc=
ing 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.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

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"
>=20
> 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.)
>=20
>             regards, tom lane
>=20

I see your point about the fuzziness of deciding what constitutes a distinc=
t value before the type is determined. My proposal was so general that it w=
ould still open a can of worms.

In Farid's particular use case, it's easy to see that the values aren't dis=
tinct, because they're all textually identical. In that case, SELECT DISTIN=
CT 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 specia=
l-case hack, but I suspect that it would actually cover most cases where un=
defined 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 t=
hat varies by row would usually come from real tables, where types are alre=
ady defined.

On Mon, Jun 7, 2010 at 3:52 PM, Robert Haas <robertmhaas@gmail.com> 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