Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dDxwK-0004AB-O7 for pgsql-sql@arkaria.postgresql.org; Thu, 25 May 2017 18:56:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dDxwK-0000vo-4U for pgsql-sql@arkaria.postgresql.org; Thu, 25 May 2017 18:56:52 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dDxvJ-0007Um-EF for pgsql-sql@postgresql.org; Thu, 25 May 2017 18:55:49 +0000 Received: from mail-pf0-x22f.google.com ([2607:f8b0:400e:c00::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dDxvG-0005ma-M6 for pgsql-sql@postgresql.org; Thu, 25 May 2017 18:55:48 +0000 Received: by mail-pf0-x22f.google.com with SMTP id m17so175164285pfg.3 for ; Thu, 25 May 2017 11:55:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:subject:thread-topic:thread-index:date:message-id :references:in-reply-to:content-language:mime-version; bh=4qjGAdaTzQBFdhof5L0Qc9wnyrhYBqZJKPSXk1/sPpU=; b=TlaGspxixcEhElSb0nYgZVvscbQax8Xl+BGX9IKmUJ3xY1iSl2JeSTsKGysQn7wYfS Rv2Ot2/3JVBkDoqWoYj0x6KFWNo5Z2TGmvKa7qQDpRlSfuM3acn2gF5dlVGcbzmyqhWl K8ynr5NOQkdarm5CjW8KhH+knLzHyvydIlFXSW7f4gHnUW47WRPLzLtcoDrHs9Q6hAzU /uEAl8Xm+Hsp52o5ArO8pGkil7flRK8pDdT0bltI9fn9nSzM78ZiuGttznRn8VXZIR7/ crRwpMrgxK4sheEJ1JlFDc/6HwdYM7eYdnd/SdJKDJMO+Pjl0W4XxP9Z9X+yKQ1bAS6E 6DmA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:thread-topic:thread-index :date:message-id:references:in-reply-to:content-language :mime-version; bh=4qjGAdaTzQBFdhof5L0Qc9wnyrhYBqZJKPSXk1/sPpU=; b=Jgq5etG3ZzSxux9A+IgbD27A2mZSSuhpdEvqq4byDi5vfO1E13cZXP9fOI47A1C/j4 ErBx6HQvyMPKVH+scyZd1hBpi0Nf7soKl08tUA6uMvqquC0g74NvC7BUyH5nF71L0V6/ VhIIqN3wPyoNu2JeOiWZfCAs8h70IUyfNgdeXxM0g/2XaxeDB0KloPavlPpE6L3Tx7x0 WF+hHr3hUP73OLLUmcj/+5hMXjNLIXOLC5JXw43zb9Zq4ixOQh3/LhZmGX1w+v1ZLKwJ BO0f2ooDG795BNHthjFvwFyqMRia6WQZG9KT/Pce/8p1MeHazovuXz4s+mfN2IcohBPn TKuQ== X-Gm-Message-State: AODbwcCmdb05nyLebYFWwudWNk1KMiC7JI6Ce72OJQgXTluUWp4Z6QgI IPL+2TkvzsltrEKjV7H7wg== X-Received: by 10.99.112.3 with SMTP id l3mr6694482pgc.13.1495738545427; Thu, 25 May 2017 11:55:45 -0700 (PDT) Received: from AM5P195MB0052.EURP195.PROD.OUTLOOK.COM ([40.101.61.93]) by smtp.gmail.com with ESMTPSA id r21sm14291814pfg.95.2017.05.25.11.55.43 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 25 May 2017 11:55:44 -0700 (PDT) From: Vladimir Nicolici To: Tom Lane CC: "pgsql-sql@postgresql.org" Subject: Re: Re: SQL error: function round(double precision, integer) does not exist Thread-Topic: [SQL] Re: SQL error: function round(double precision, integer) does not exist Thread-Index: ATA2RDAwY68H4oFWaPnM164+C7VH4Tg0NTEwMDM0NzA3NjUxOMBBkrOJ X-MS-Exchange-MessageSentRepresentingType: 2 Date: Thu, 25 May 2017 18:23:33 +0000 Message-ID: References: ,<16203.1495734888@sss.pgh.pa.us> ,<16285.1495736557@sss.pgh.pa.us> In-Reply-To: <16285.1495736557@sss.pgh.pa.us> Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: Content-Type: multipart/alternative; boundary="_000_DB6P195MB005628CFDA615427B737C4FDF7FF0DB6P195MB0056EURP_" MIME-Version: 1.0 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_000_DB6P195MB005628CFDA615427B737C4FDF7FF0DB6P195MB0056EURP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable In my opinion the integer version doesn=92t prevent the fraction problem at= all, since the rounding works on fractions, even when the result is an int= eger. In fact it seems to introduce what I would consider a bug, compared t= o doing the conversion explicitly, for example in this query: select round(0.5::real), round(0.5::double precision), round((0.5::double p= recision)::numeric), round(0.5) The result will be 0, 0, 1, 1, which is very strange. I think, at the very least, the implicit and explicit conversions, -- round= (0.5::double precision) and round((0.5::double precision)::numeric) -- shou= ld give the same result, and they don=92t. Which reinforces my intuition th= at I should avoid the real and double precision data types whenever possibl= e. Thanks, Vlad From: Tom Lane Sent: Thursday, May 25, 2017 21:22 To: Vladimir Nicolici Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Re: SQL error: function round(double precision, integer)= does not exist Vladimir Nicolici writes: > I do that, but it=C2=92s extremely annoying. Well, if it rises to the level of extreme annoyance for you, there is a simple solution: create function round(float8, int) returns numeric as 'select round($1::numeric, $2)' language sql; > Furthermore, since the single parameter version accepts double precision = numbers, the imprecise representation excuse seems questionable at best. The single-parameter version always rounds to integer, which avoids the problem of not being able to represent decimal fractions exactly. regards, tom lane --_000_DB6P195MB005628CFDA615427B737C4FDF7FF0DB6P195MB0056EURP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

In my opinion the integer version doesn=92t prevent = the fraction problem at all, since the rounding works on fractions, even wh= en the result is an integer. In fact it seems to introduce what I would con= sider a bug, compared to doing the conversion explicitly, for example in this query:

 

select round(0.5::real), round(0.5::double precision= ), round((0.5::double precision)::numeric), round(0.5)

 

The result will be 0, 0, 1, 1, which is very strange= .

 

I think, at the very least, the implicit and explici= t conversions, -- round(0.5::double precision) and round((0.5::double preci= sion)::numeric) -- should give the same result, and they don=92t. Which rei= nforces my intuition that I should avoid the real and double precision data types whenever possible.

 

Thanks,

Vlad

 

 

From: Tom Lane
Sent: Thursday, May 25, 2017 21:22
To: Vladimir Nicolici
Cc: pgsql-sql@postgresql= .org
Subject: Re: [SQL] Re: SQL error: function round(double precision, i= nteger) does not exist

 

Vladimir Nicolici &= lt;vladnc@gmail.com> writes:
> I do that, but it=C2=92s extremely annoying.

Well, if it rises to the level of extreme annoyance for you, there
is a simple solution:

create function round(float8, int) returns numeric as
'select round($1::numeric, $2)' language sql;

> Furthermore, since the single parameter version accepts double precisi= on numbers, the imprecise representation excuse seems questionable at best.=

The single-parameter version always rounds to integer, which avoids the
problem of not being able to represent decimal fractions exactly.

            &nb= sp;           regards, to= m lane

 

--_000_DB6P195MB005628CFDA615427B737C4FDF7FF0DB6P195MB0056EURP_--