Thread: BUG #18689: psql : operator "!=" do not behave like "<>"

The following bug has been logged on the website:

Bug reference:      18689
Logged by:          Joël HECHT
Email address:      jhecht@cirilgroup.com
PostgreSQL version: 15.6
Operating system:   CentOS Linux release 7.9
Description:

Hello,

according to the documentation, operators != and <> are supposed to be
interchangeable.
But when operator "!=" is followed by a negative number, the request failed
with an error "No operator matches the given name".

Here is how to reproduce the issue directly with psql :


[postgres@d04f96727d36 ~]$ psql
psql (15.6)
Type "help" for help.

postgres=# select -1<>-1;
 ?column?
----------
 f
(1 row)

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.
postgres=#


Regards.

Rafia Sabih <rafia.pghackers@gmail.com> writes:
> Thank you for reporting this. However I found that the lack of space in the
> statement is causing this.
> On trying it like select -1 != -1; it works fine.

Right, see

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

particularly the bit about "A multiple-character operator name cannot
end in + or -, unless ...".  "!=-" will be treated as one operator,
but "<>-" will not.

            regards, tom lane

On 2024-11-05 14:02 +0100, Rafia Sabih wrote:
> Thank you for reporting this. However I found that the lack of space in the
> statement is causing this.
> On trying it like select -1 != -1; it works fine.

The docs explain it in a bit more detail:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

"<>" is the standard inequality operator, whereas the operator name "!="
is just an alias implemented by Postgres (and some other RDBMS).  That
alias is subject to the same restrictions as outlined in the linked
docs, i.e., Postgres parses the longer operator name "!=-" because it
contains "!".

> On Tue, 5 Nov 2024 at 13:39, PG Bug reporting form <noreply@postgresql.org>
> wrote:
> 
> > The following bug has been logged on the website:
> >
> > Bug reference:      18689
> > Logged by:          Joël HECHT
> > Email address:      jhecht@cirilgroup.com
> > PostgreSQL version: 15.6
> > Operating system:   CentOS Linux release 7.9
> > Description:
> >
> > Hello,
> >
> > according to the documentation, operators != and <> are supposed to be
> > interchangeable.
> > But when operator "!=" is followed by a negative number, the request failed
> > with an error "No operator matches the given name".
> >
> > Here is how to reproduce the issue directly with psql :
> >
> >
> > [postgres@d04f96727d36 ~]$ psql
> > psql (15.6)
> > Type "help" for help.
> >
> > postgres=# select -1<>-1;
> >  ?column?
> > ----------
> >  f
> > (1 row)
> >
> > 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.
> > postgres=#

-- 
Erik