X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 616CE9DC833 for ; Wed, 28 Dec 2005 23:03:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04227-05 for ; Wed, 28 Dec 2005 23:03:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 411C99DC83E for ; Wed, 28 Dec 2005 23:03:15 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBT33Jd1003363; Wed, 28 Dec 2005 22:03:19 -0500 (EST) To: "George Pavlov" cc: pgsql-sql@postgresql.org Subject: Re: DISTINCT with NULLs and INT fields In-reply-to: References: Comments: In-reply-to "George Pavlov" message dated "Wed, 28 Dec 2005 18:00:59 -0800" Date: Wed, 28 Dec 2005 22:03:19 -0500 Message-ID: <3362.1135825399@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.052 required=5 tests=[AWL=0.052] X-Spam-Score: 0.052 X-Spam-Level: X-Archive-Number: 200512/237 X-Sequence-Number: 23779 "George Pavlov" writes: > the interesting thing here is that 4::int gets into a text > field whereas 4::text does not get into an integer field. seems to me > like there is an implicit int-to-text cast (without a symmetrical > text-to-int one) Yeah, there is. You can easily see the list of implicit casts for yourself: select castsource::regtype, casttarget::regtype from pg_cast where castcontext = 'i' and castsource != casttarget; There's 90 of them in current CVS tip, and most of 'em are unsurprising within-type-category casts, such as implicit promotion of int to bigint. However we have about a dozen implicit casts to text from the numeric and datetime type categories. Personally I would dearly love to get rid of these, because they are accidents waiting to happen (and they do regularly bite people, see the mail list archives for evidence). But it seems people expect to be able to do things like number || ' string' without explicitly casting the number to text. regards, tom lane