DISTINCT with NULLs and INT fields

From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: DISTINCT with NULLs and INT fields
Date: 2005-12-28 19:57:38
Message-ID: CCB89282FCE1024EA3DCE687A96A5164039EC532@ehost010-6.exch010.intermedia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

What does The SQL Standard say about this one?

create table foo (a varchar, b int);

insert into foo (a, b) select null, null from bar;
-- no problem

insert into foo (a, b) select distinct null, null from bar;
-- ERROR: column "b" is of type integer but expression is of type text
-- HINT: You will need to rewrite or cast the expression.

It seems that applying DISTINCT makes the NULL be of type TEXT... I just
tried the exact same thing in Ms. SQL Server 2005 and it works with no
errors. Looks like SQL Server makes the NULLs be INTs, but is probably
better at doing the implicit conversion from INT. Any thoughts on what
the "correct" behavior should be here?

George

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2005-12-28 20:19:29 Re: DISTINCT with NULLs and INT fields
Previous Message Don Croata 2005-12-28 19:37:21 Re: Cursors and recursion