Skip site navigation (1) Skip section navigation (2)

Re: NAB : insert into <table> select distinct <fields> => when used on null, distinct causes loss of type knowledge

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: NAB : insert into <table> select distinct <fields> => when used on null, distinct causes loss of type knowledge
Date: 2004-08-24 21:30:56
Message-ID: 12269.1093383056@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> So it seems distinct applied to the second column causes it to lose knowledge
> on its type. 

No, because it never had any: NULL is typeless (type UNKNOWN, to the
parser).  In the straight INSERT this doesn't matter because we don't
have to resolve the type until we get up to the INSERT, and then we know
we want to insert into the value column.  But to do a DISTINCT, the
parser has to assign datatypes to all the columns (to determine the
comparison rules).  The default assumption for an UNKNOWN constant is
type TEXT.  This is chosen based on the assumption that when someone
writes
	select distinct 'foo';
they are probably expecting the system to treat 'foo' as a TEXT literal.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Christine DesmukeDate: 2004-08-24 21:33:47
Subject: Gentoo for production DB server?
Previous:From: Stephan SzaboDate: 2004-08-24 21:26:57
Subject: Re: How is this possible? (more on deadlocks)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group