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 25DB89DC8A4 for ; Wed, 28 Dec 2005 16:46:31 -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 36978-08 for ; Wed, 28 Dec 2005 16:46:32 -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 077389DC845 for ; Wed, 28 Dec 2005 16:46:28 -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 jBSKkTO1021700; Wed, 28 Dec 2005 15:46:29 -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 12:37:29 -0800" Date: Wed, 28 Dec 2005 15:46:29 -0500 Message-ID: <21699.1135802789@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.051 required=5 tests=[AWL=0.051] X-Spam-Score: 0.051 X-Spam-Level: X-Archive-Number: 200512/227 X-Sequence-Number: 23769 "George Pavlov" writes: > indeed! but, wait, doesn't our favorite dbms do some implicit casting > too? continuing with my table foo (a varchar, b int): > test=# delete from foo; > DELETE 2 > test=# insert into foo values (4,4); > INSERT 0 1 > test=# insert into foo values ('4','4'); > INSERT 0 1 > test=# insert into foo values ('oh','no'); > ERROR: invalid input syntax for integer: "no" Sure, but in this example the required type of the value is clear from immediate context (ie, the INSERT). This is one of the cases where the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4' in this example are *not* values of type text; they are untyped literals which we eventually resolve as varchar or int.) The other case you mentioned is one where we are going out on a limb a bit: INSERT INTO foo SELECT NULL, NULL; In this case we allow the SELECT to not force a type choice, so that the INSERT sees the raw untyped values, but I think that this query is probably illegal per spec --- I believe the spec requires a SELECT to deliver well-defined data types. Too lazy to go look up chapter and verse at the moment. regards, tom lane