Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hptFN-0004Ta-Rg for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:46:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hptFM-0000r3-KM for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:46:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hptFM-0000lH-6W for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:46:20 +0000 Received: from mout.kundenserver.de ([212.227.126.135]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1hptFH-0000LT-NO for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:46:19 +0000 Received: from [10.8.0.6] ([185.40.248.10]) by mrelayeu.kundenserver.de (mreue009 [212.227.15.129]) with ESMTPSA (Nemesis) id 1MpUQm-1iCoPe0SYG-00ps0c for ; Tue, 23 Jul 2019 13:46:10 +0200 Subject: Re: Implicit typecast behavior To: pgsql-sql@lists.postgresql.org References: From: Thomas Kellerer Message-ID: <79bd73eb-5475-5e1f-8999-070db1725f63@gmx.net> Date: Tue, 23 Jul 2019 13:46:09 +0200 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: en-GB Content-Transfer-Encoding: 7bit X-Provags-ID: V03:K1:gRgnKG5SLV4KimkbcYIES5gMgcwo3oQECmkKkanXDDqULs96HdP uF4ko3+EcRuKkdqzLGLDjOuNPxfRQP628DnAcIp7QxqKKSw681ep5tDmW6Payx6On5zJ7Ui c6LoTGh4P+F5unOIhgRwWZjZzDM/c7bQrMCD1YZsHrTefuIXEfYeFAyWh4lO+KFNoF/2KZ8 IJ9VPWE4brPc/ybAZGC/Q== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:IRRPQj+5GVc=:eI9xCdC9z6CTZCoO/keHQs i4tAm3V36wxNt9zACYZ8HyJ73YgJ7n7n+r4+vTFVW1Kh8vv/SM79gv1OYvxgEO0wghLmKBxbz D4R7Yeub8iulUXAfblrt1y78PwOTtLFH8VreFro6f7B8HT9jA/N1GcSHQMZ0nfevGYEYD84Kn Yf0PulhKahmLBphfLYL7TqHmeM7y3otvLvWCCt1ebTUdyeV/e0UBtn2RsSV/TzmmQcU14qT+G 4X/Fh2eECJa9RKYyuDl1HxTCaorWU3tIc8ZfzdKk+r/9CuujBS3eW1cHMrV8NrRUYhvWZ5kWM YTTJe5mfHypLbO4H6zpVDtkHScR4/YSphpq39z2v4aZ8ZcmJJ78BhkYpdVdtlr+3P8uXxamlQ pTyy1Od+gtlaXLkkUFpZO7fJvKn0BN9hC3dixX4hJAZ7ITfTmAUKZ2kmqO33BaKWm7JOn85ko ToYyr+HOc04OS+KVkFUGHTlieQCdTny/YJelyfqoopHkv75PUTxnwxv3qnl9ApFkpIxmvjnSI 2GG3NP5nL53n7t3V/whLuegvqgx2MLdui0tvKfSo7uaf1IBi/JXEzuRbpJU6E0Icu9kLYc6ud qIjZfRYsI83e76xRpkvIFCeY0iJ3FjCTqpCkQGrPaKfbSYh+tF2cShZTJCWWUM0RUYZjIvzJY WRwhljkfeFSB1cs+j4OjqUqQ4j67mlqWxtIFHYRQzVOnDVszgRD5rhGKI8ovpsUQEZqgAMwaQ qVkyHcBj0mqu0JpCEsTQEl3oQBB0QMzCipiA1hvG4TwnvIM1FktiQtOF1WY= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Rick Vincent schrieb am 23.07.2019 um 13:22: > > I am looking for a way to make postgresql function as Oracle and > other databases do with implicit typecasts. For example, in the > query below: > > SELECT RECID, RANK FROM MYTABLE WHERE RANK > 12 I know that you probably don't want to hear this, but: That is really bad coding style in Oracle just as well as in Postgres as well. It was a bug waiting to happen in Oracle already - the query will fail if there is at least one row where RANK can not be converted to number. The correct solution to that problem is to not store numbers in VARCHAR columns - full stop. Don't do it. Not in Postgres not in any other database. Do the right thing and convert that column to an integer column. Thomas