Thread: bit|varbit #, xor operator

Related to [1], I want to add an operator that returns the count of set 
(or unset) bits in a bit|varbit input. Given the number of times people 
ask "how can I get a count of NULL fields" and similar, I expect this to 
become quite popular. The obvious choice would be to use #, but I was 
rather surprised to discover # is already used for XOR.

I think ^ is a pretty standard convention for XOR, but I can understand 
why we wouldn't want to just use that. But I also think the choice of # 
is pretty unfortunate.

# is currently mixed between XOR and counting[2]. IMHO we should 
disambiguate it. My bet is that the XOR versions are very seldom used 
and could be renamed without much grumbling. The versions that actually 
do counting are presumably more popular. I'm not sure about the 
intersection versions.

Personally I think it was a mistake to use # for intersection. Range 
doesn't do that (using * instead), and AFAICT PostGIS doesn't either 
(preferring &). So I propose renaming those operators, as well as the 
XOR ones. I think ^^ is pretty logical for XOR. I'm not sure about 
intersect... * doesn't seem like a good idea, && is overlaps, maybe &*.

Related to this I'd also like to add a boolean XOR operator as that's a 
relatively common request/question.

1: 
https://www.postgresql.org/message-id/9f1db94a-fa18-5975-3a6c-6850fabb3865%40BlueTreble.com
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> Personally I think it was a mistake to use # for intersection. Range 
> doesn't do that (using * instead), and AFAICT PostGIS doesn't either 
> (preferring &). So I propose renaming those operators, as well as the 
> XOR ones. I think ^^ is pretty logical for XOR. I'm not sure about 
> intersect... * doesn't seem like a good idea, && is overlaps, maybe &*.

I'm pretty much -1 on renaming any of these existing operators.  There's
no realistic hope of having only one interpretation for an operator name
across all the different data types --- the best we can hope for, I think,
is consistency within a datatype family.  For example, the reason not to
use ^ for integer XOR is that it also means exponentiation for
float/numeric, which are in the same class of datatypes.  And your
proposal of ^^ doesn't satisfy anyone's notion of least astonishment.
As for renaming intersection, renaming operators that have had those names
since Berkeley, and are perfectly consistent within their datatype family,
seems likely to create much more pain than it removes.

As for counting bits in a bitstring, why do we have to make that an
operator at all?  Using a function would decrease the stress involved
in choosing a name, and it's hard to believe that the requirement is
so common that we need to shave a few keystrokes.  But if you must have
an operator there's not that much wrong with using prefix # for it.

> Related to this I'd also like to add a boolean XOR operator as that's a 
> relatively common request/question.

We have boolean XOR; it's spelled "<>".
        regards, tom lane

On 10/16/16 3:13 PM, Tom Lane wrote:
> As for counting bits in a bitstring, why do we have to make that an
> operator at all?  Using a function would decrease the stress involved
> in choosing a name, and it's hard to believe that the requirement is
> so common that we need to shave a few keystrokes.  But if you must have
> an operator there's not that much wrong with using prefix # for it.

Fair enough.

>> > Related to this I'd also like to add a boolean XOR operator as that's a
>> > relatively common request/question.
> We have boolean XOR; it's spelled "<>".

I always forget about that... though, it doesn't work for boolean arrays.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@bluetreble.com> writes:
> On 10/16/16 3:13 PM, Tom Lane wrote:
>>> Related to this I'd also like to add a boolean XOR operator as that's a
>>> relatively common request/question.

>> We have boolean XOR; it's spelled "<>".

> I always forget about that...

Maybe it should be mentioned explicitly in the docs.

> though, it doesn't work for boolean arrays.

Define doesn't work?
        regards, tom lane

On 10/17/16 11:29 AM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@bluetreble.com> writes:
>> On 10/16/16 3:13 PM, Tom Lane wrote:
>>>> Related to this I'd also like to add a boolean XOR operator as that's a
>>>> relatively common request/question.
>
>>> We have boolean XOR; it's spelled "<>".
>
>> I always forget about that...
>
> Maybe it should be mentioned explicitly in the docs.

Hrm, went to go add it and it appears we don't have a section for 
boolean type operators. I guess I should add it to 9.1?

>> though, it doesn't work for boolean arrays.
>
> Define doesn't work?

I would expect array[true, false] XOR array[true, true] to return 
array[false, true], but <> just returns a single true (since the arrays 
are !=).

Though, I guess what would make the most sense there is a map function 
that would apply an operator or function to every element of a set of 
arrays. But I don't see any way to do that without major changes to how 
anyarray works.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Sun, Oct 16, 2016 at 4:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> Personally I think it was a mistake to use # for intersection. Range
>> doesn't do that (using * instead), and AFAICT PostGIS doesn't either
>> (preferring &). So I propose renaming those operators, as well as the
>> XOR ones. I think ^^ is pretty logical for XOR. I'm not sure about
>> intersect... * doesn't seem like a good idea, && is overlaps, maybe &*.
>
> I'm pretty much -1 on renaming any of these existing operators.

I'm *definitely* -1 on renaming any of these existing operators.

> As for renaming intersection, renaming operators that have had those names
> since Berkeley, and are perfectly consistent within their datatype family,
> seems likely to create much more pain than it removes.

Agreed.

> As for counting bits in a bitstring, why do we have to make that an
> operator at all?  Using a function would decrease the stress involved
> in choosing a name, and it's hard to believe that the requirement is
> so common that we need to shave a few keystrokes.  But if you must have
> an operator there's not that much wrong with using prefix # for it.

Yeah, I think the value of operators other than the basic arithmetic
and logical operations is very low.  I mean, if it's not going to be
familiar to people based on their general knowledge of mathematics
and/or other programming languages, it's actually easier to find a
function than it is to find an operator.  If I want a function that
does something to do with counting, I can type:

\df *count*

If I want an operator that does something similar, I'm out of luck:
\do *count* looks for operators that contain count in the operator
name, not the description.  Yeah, that could be changed, but
bits_count() is a lot easier to remember than # or whatever.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company