469,332 Members | 6,612 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

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

Hi,
Not exactly a showstopper, but I noticed this behaviour:

db=# create table f1 (id int, value int);
CREATE TABLE

db=# insert into f1 select 1 as id, null;
INSERT 25456306 1

db=# insert into f1 select distinct 2 as id, null;
ERROR: column "value" is of type integer but expression is of type text
HINT: You will need to rewrite or cast the expression.

db=# insert into f1 select distinct on (id) 2 as id, null;
INSERT 25456307 1

So it seems distinct applied to the second column causes it to lose knowledge
on its type.

Does anybody happen to know why ?


--
Best,


Frank.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 4659
Frank van Vugt <ft**********@foxi.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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
Hi Tom,
No, because it never had any: NULL is typeless (type UNKNOWN, to the
parser). 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.


I grok, thanks for the quick reply.

--
Best,


Frank.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

61 posts views Thread by Toby Austin | last post: by
2 posts views Thread by Bart Plessers \(artabel\) | last post: by
7 posts views Thread by | last post: by
1 post views Thread by ghadley_00 | last post: by
16 posts views Thread by Tantale | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.