'!=' and '<>' operators in postgresql

user_12467602 0
Viewed 905

According to postgresql manual != means the same as <>. In reality, it doesn't seems to be the case:

psql=> select 1 where 1!=-1;
ERROR:  operator does not exist: integer !=- integer
LINE 1: select 1 where 1!=-1;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
psql=> select 1 where 1<>-1;
 ?column? 
----------
        1
(1 row)

Is this a bug or this is an expected behavior that wasn't covered by the manual?

postgresql sql database
2 Answers

You need to write a space so that postgres knows, that it's a != operator and not !=-:

select 1 != -1;

Or you can put it in parentheses:

select 1!=(-1);
user_76067640

The SQL standard operator is <>, so it is a good habit to stick with it.

The reason for the behavior difference is explained in the documentation:

There are a few restrictions on your choice of [operator] name:

  • A multicharacter operator name cannot end in + or -, unless the name also contains at least one of these characters:

    ~ ! @ # % ^ & | ` ?

    For example, @- is an allowed operator name, but *- is not. This restriction allows PostgreSQL to parse SQL-compliant commands without requiring spaces between tokens.

Now since <> does not contain any of these characters, PostgreSQL knows that <>- cannot be an operator name, and there is no ambiguity in parsing.

user_64643080
Related