Postgres Pro
Downloads
Home   >   mailing lists

Thread: differences between <> and != when using signed values on the right hand side

differences between <> and != when using signed values on the right hand side

From
David Hunnisett
Date:
When using a signed comparison != does not behave like <> if the right hand side comparator is signed and there is no space between the comparator and the sign

<>+1
<>-1 
Both bahave as the inverse to
=+1
=-1
But 
!=-1
!=+1
Throw errors.


Tested with 16.3,13.15 and 9.6.24


postgres=# select version();

                                                          version                                                          

---------------------------------------------------------------------------------------------------------------------------

 PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

(1 row)


postgres=# select 1<>-1.0;

 ?column? 

----------

 t

(1 row)


postgres=# select 1<>-1;

 ?column? 

----------

 t

(1 row)


postgres=# select 1!=-1.0;

ERROR:  operator does not exist: integer !=- numeric

LINE 1: select 1!=-1.0;

                ^

HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

postgres=# select 1!=-1;

ERROR:  operator does not exist: integer !=- integer

LINE 1: select 1!=-1;

                ^

HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.


Adding a space does produce the expected answer


select 1!= -1.0;

 ?column? 

----------

 t

(1 row)



Re: differences between <> and != when using signed values on the right hand side

From
"David G. Johnston"
Date:
On Thursday, May 16, 2024, David Hunnisett <david.hunnisett@probit.io> wrote:
When using a signed comparison != does not behave like <> if the right hand side comparator is signed and there is no space between the comparator and the sign


This is not a bug.  You are seeing practical examples of the behavior documented here:


In short, a consequence of allowing users to define their own operators.

David J.

Re: differences between <> and != when using signed values on the right hand side

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, May 16, 2024, David Hunnisett <david.hunnisett@probit.io>
> wrote:
>> When using a signed comparison != does not behave like <> if the right
>> hand side comparator is signed and there is no space between the comparator
>> and the sign

> This is not a bug.  You are seeing practical examples of the behavior
> documented here:
> https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

Yeah, it would probably have been better to forbid multicharacter
operator names that end in '+' or '-'.  But that ship sailed a long
time ago, so we have this kluge to (effectively) allow it only if
the operator can't be a SQL-standard one.  Thus, in the example
"!=-" is being read as a single operator name.

            regards, tom lane