Thread: select distinct null

Hello!

I've got problem with such a query:

INSERT INTO table1 VALUES SELECT DISTINCT null,table2.atr1 FROM table2
WHERE table2.atr1 > 0;

It works in PostgreSQL 6.4.2 but in 7.0.3 it returns:

ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
        Use an explicit ordering operator or modify the query

I discovered that PostgreSQL 7.0.3 doesn't accept the querry:

select distinct null;

and reurns the same error.

I use such queries in some client applications and it would be hard
to rewrite them. Could anyone explain to me what has been changed in 7.0.3
and how can I solve the problem on the server side.

Thanks in advance.

Karol Wieloch

----- Original Message -----
From: <karol@gmweltel.com.pl>

> INSERT INTO table1 VALUES SELECT DISTINCT null,table2.atr1 FROM table2
> WHERE table2.atr1 > 0;
>
> It works in PostgreSQL 6.4.2 but in 7.0.3 it returns:
>
> ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
>         Use an explicit ordering operator or modify the query
>
> I discovered that PostgreSQL 7.0.3 doesn't accept the querry:
>
> select distinct null;
>

Casting the null seems to do it.

select distinct null::text,a from foo;

works OK on my 7.0 here


- Richard Huxton

On Thu, 4 Jan 2001, Richard Huxton wrote:
>Casting the null seems to do it.
> select distinct null::text,a from foo;
> works OK on my 7.0 here

Thanks! It works on my too so I will have hardly any trouble to fix the
queries.

But I'm just wondering if there is some solution not involving
any modification of the query code.

Karol Wieloch

<karol@gmweltel.com.pl> writes:
> On Thu, 4 Jan 2001, Richard Huxton wrote:
>> Casting the null seems to do it.
>> select distinct null::text,a from foo;
>> works OK on my 7.0 here

> Thanks! It works on my too so I will have hardly any trouble to fix the
> queries.
> But I'm just wondering if there is some solution not involving
> any modification of the query code.

Update to 7.1 ...

            regards, tom lane