Re: Direct converting numeric types to bool

[Tom Lane]

n.zhuch...@postgrespro.ru writes:
> Yes, in postgres already exists int::boolean casting and in case:
> select 10::bigint::int::boolean;
> it will perfectly work. But if you want to cast more significant number:
> select (2^32)::bigint::int::boolean;
> you receive the boundary value error.

Right.  I don't see a reason why we shouldn't create a bigint-to-bool
cast to fix that, and smallint-to-bool maybe for completeness.  However,
I'm less excited about float or numeric to bool, because I don't think
there is any very principled argument about what such a cast should do
with infinities, NaNs, or denormalized numbers.  Somebody who knows
what should happen for their own application can create their own cast
that handles those cases ... but I'm unsure that there's a one-size-
fits-all answer that we could put into a default behavior.

regards, tom lane

Re: Direct converting numeric types to bool

[n . zhuchkov]

Thanks for your feedback!
Really, support migration from Oracle isn't the best argumentation for 
this patch.

Yes, in postgres already exists int::boolean casting and in case:

select 10::bigint::int::boolean;

it will perfectly work. But if you want to cast more significant number:

select (2^32)::bigint::int::boolean;

you receive the boundary value error.
Also in operations with floating-point numbers, may arise a fault in the 
results of calculations, because of which the number will always be cast 
in true
Thus it sounds like not a bad idea to support direct casting from 
numeric types to boolean. What do you think?


Alex Ignatov писал 2018-02-28 18:23:

-Original Message-
From: n.zhuch...@postgrespro.ru [mailto:n.zhuch...@postgrespro.ru]
Sent: Wednesday, February 28, 2018 6:04 PM
To: pgsql-hackers 
Subject: Direct converting numeric types to bool

Attached patch allow direct convertion of numeric types to bool like
integer::bool.
Supported types:
  - smallint;
  - bigint;
  - real;
  - double precision;
  - decimal(numeric).

This functionality is helped with migration from Oracle.

--
Nikita Zhuchkov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

Hello!

What prevent us from:

postgres=# select 1::bigint::int::boolean;
 bool
--
 t
(1 row)

It is just one additional casting and required no additional patching
--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

RE: Direct converting numeric types to bool

[Alex Ignatov]

-Original Message-
From: n.zhuch...@postgrespro.ru [mailto:n.zhuch...@postgrespro.ru] 
Sent: Wednesday, February 28, 2018 6:04 PM
To: pgsql-hackers 
Subject: Direct converting numeric types to bool

Attached patch allow direct convertion of numeric types to bool like
integer::bool.
Supported types:
  - smallint;
  - bigint;
  - real;
  - double precision;
  - decimal(numeric).

This functionality is helped with migration from Oracle.

--
Nikita Zhuchkov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

Hello!

What prevent us from:

postgres=# select 1::bigint::int::boolean;
 bool
--
 t
(1 row)

It is just one additional casting and required no additional patching
--
Alex Ignatov 
Postgres Professional: http://www.postgrespro.com 
The Russian Postgres Company

Re: Direct converting numeric types to bool

[Pavel Stehule]

2018-02-28 16:13 GMT+01:00 Pavel Stehule :

> Hi
>
> 2018-02-28 16:06 GMT+01:00 :
>
>> n.zhuch...@postgrespro.ru писал 2018-02-28 18:04:
>>
>> Attached patch allow direct convertion of numeric types to bool like
>>> integer::bool.
>>> Supported types:
>>>  - smallint;
>>>  - bigint;
>>>  - real;
>>>  - double precision;
>>>  - decimal(numeric).
>>>
>>> This functionality is helped with migration from Oracle.
>>>
>>
> Looks little bit obscure to upstream code (can lives as extension outside)
>
> all work can be done be function
>
> CREATE OR REPLACE FUNCTION public.to_bool(anyelement)
>  RETURNS boolean
>  LANGUAGE sql
>  IMMUTABLE STRICT
> AS $function$
> select $1::int::boolean $function$
>
> I really doesn't see any sense to allow cast from double to boolean
>

Long time Oracle had not boolean, so some ugly tricks was necessary there.
There are not reason do same in Postgres.


>
> -1 from me
>
> Regards
>
> Pavel
>
>

Re: Direct converting numeric types to bool

[Pavel Stehule]

Hi

2018-02-28 16:06 GMT+01:00 :

> n.zhuch...@postgrespro.ru писал 2018-02-28 18:04:
>
> Attached patch allow direct convertion of numeric types to bool like
>> integer::bool.
>> Supported types:
>>  - smallint;
>>  - bigint;
>>  - real;
>>  - double precision;
>>  - decimal(numeric).
>>
>> This functionality is helped with migration from Oracle.
>>
>
Looks little bit obscure to upstream code (can lives as extension outside)

all work can be done be function

CREATE OR REPLACE FUNCTION public.to_bool(anyelement)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
select $1::int::boolean $function$

I really doesn't see any sense to allow cast from double to boolean

-1 from me

Regards

Pavel

Re: Direct converting numeric types to bool

[Tom Lane]

n.zhuch...@postgrespro.ru writes:
> Attached patch allow direct convertion of numeric types to bool like 
> integer::bool.
> Supported types:
>   - smallint;
>   - bigint;
>   - real;
>   - double precision;
>   - decimal(numeric).
> This functionality is helped with migration from Oracle.

I think you forgot to attach the patch, but in any case: is this
really a behavior we want?  "Oracle has it" is not a good argument
in my view, nor do I recall people complaining that they need such
a behavior to migrate.

regards, tom lane

Re: Direct converting numeric types to bool

[n . zhuchkov]

n.zhuch...@postgrespro.ru писал 2018-02-28 18:04:

Attached patch allow direct convertion of numeric types to bool like
integer::bool.
Supported types:
 - smallint;
 - bigint;
 - real;
 - double precision;
 - decimal(numeric).

This functionality is helped with migration from Oracle.
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 686528c..6cbfcf5 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -2970,3 +2970,83 @@ CREATE VIEW user_mappings AS
 FROM _pg_user_mappings;
 
 GRANT SELECT ON user_mappings TO PUBLIC;
+
+-- bool --> smallint(int2)
+CREATE OR REPLACE FUNCTION bool_smallint(boolean)
+  RETURNS smallint AS
+  'bool_int2'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (boolean AS smallint) WITH FUNCTION bool_smallint(boolean) as implicit;
+
+-- smallint(int2) --> bool
+CREATE OR REPLACE FUNCTION smallint_bool(smallint)
+  RETURNS bool AS
+  'int2_bool'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (smallint AS boolean) WITH FUNCTION smallint_bool(smallint) as implicit;
+
+-- bigint(int8) --> bool
+CREATE OR REPLACE FUNCTION bool_bigint(boolean)
+  RETURNS bigint AS
+  'bool_int8'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (boolean AS bigint) WITH FUNCTION bool_bigint(boolean) as implicit;
+
+-- bool --> bigint(int8)
+CREATE OR REPLACE FUNCTION bigint_bool(bigint)
+  RETURNS bool AS
+  'int8_bool'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (bigint AS boolean) WITH FUNCTION bigint_bool(bigint) as implicit;
+
+-- double precision(float8) --> bool
+CREATE OR REPLACE FUNCTION double_precision_bool(double precision)
+  RETURNS bool AS
+  'float8_bool'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (double precision AS boolean) WITH FUNCTION double_precision_bool(double precision) as implicit;
+
+-- bool --> double precision(float8)
+CREATE OR REPLACE FUNCTION bool_double_precision(boolean)
+  RETURNS double precision AS
+  'bool_float8'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (boolean AS double precision) WITH FUNCTION bool_double_precision(boolean) as implicit;
+
+-- real(float4) --> bool
+CREATE OR REPLACE FUNCTION real_bool(real)
+  RETURNS bool AS
+  'float4_bool'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (real AS boolean) WITH FUNCTION real_bool(real) as implicit;
+
+-- bool --> real(float4)
+CREATE OR REPLACE FUNCTION bool_real(boolean)
+  RETURNS real AS
+  'bool_float4'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (boolean AS real) WITH FUNCTION bool_real(boolean) as implicit;
+
+-- numeric(decimal) --> bool
+CREATE OR REPLACE FUNCTION numeric_bool(decimal)
+  RETURNS bool AS
+  'numeric_bool'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (decimal AS boolean) WITH FUNCTION numeric_bool(decimal) as implicit;
+
+-- bool --> numeric(decimal)
+CREATE OR REPLACE FUNCTION bool_numeric(boolean)
+  RETURNS decimal AS
+  'bool_numeric'
+  LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE
+  COST 1;
+CREATE CAST (boolean AS decimal) WITH FUNCTION bool_numeric(boolean) as implicit;
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index bc6a3e0..a687104 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -1331,6 +1331,53 @@ i2tof(PG_FUNCTION_ARGS)
 	PG_RETURN_FLOAT4((float4) num);
 }
 
+ /*
+ *   float4_bool - converts a float4 number to a bool
+ *   epsilon - machine epsilon gives an upper bound on the relative error
+ *   due to rounding in floating point arithmetic.
+ */
+Datum
+float4_bool(PG_FUNCTION_ARGS)
+{
+ float epsilon = 5.96e-08;
+
+ if (fabs(PG_GETARG_FLOAT4(0)) <= epsilon)
+   PG_RETURN_BOOL(false);
+ else
+ PG_RETURN_BOOL(true);
+}
+
+/*
+ *   bool_float4 - converts a bool to a float4 number
+ */
+Datum
+bool_float4(PG_FUNCTION_ARGS)
+{
+ if (PG_GETARG_BOOL(0) == false)
+   PG_RETURN_FLOAT4(0);
+ else
+   PG_RETURN_FLOAT4(1);
+}
+
+/*
+ *   float8_bool - converts a float4 number to a bool
+ *   epsilon - machine epsilon gives an upper bound on the relative error
+ *   due to rounding in floating point arithmetic.
+ */
+Datum
+float8_bool(PG_FUNCTION_ARGS)
+{
+ float epsilon = 1.11e-16;
+
+ if (fabs(PG_GETARG_FLOAT8(0)) <= epsilon)
+   PG_RETURN_BOOL(false);
+ else
+   PG_RETURN_BOOL(true);
+}
+
+/*
+ *   bool_float8 - converts a bool to a float4 number
+ */
+Datum
+bool_float8(PG_FUNCTION_ARGS)
+{
+ if (PG_GETARG_BOOL(0) == false)
+   PG_RETURN_FLOAT8(0);
+ else
+   PG_RETURN_FLOAT8(1);
+}
 
 /*
  *		===
diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index 559c365..a6ea79b 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -354,6 +354,26 @@