What is the difference between != and <> operators?

Question

I don't like not knowing this as there may be situations when I need to use one instead of the other. It seems in most cases they produce the same results but I am taking a guess they have subtle difference, maybe to do with NULL values or one does strict comparisons like the extra = in PHP does.

Thanks

Answer 1

From the manual:

Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.

So no, there is no difference between the two.

Answer 2

<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <>.

They're exactly the same in postgresql. See also the documentation.

Note though, that postgresql allows you to implement your own types and overload operators for those types, so ultimately it depends on the datatypes involved what the != and <> operator actually does, but <> and != can never do different things.

Answer 3

According to this: http://www.postgresql.org/docs/current/static/functions-comparison.html The operator != gets transformed to <> in the parse stage.

Answer 4

Being curious I found out that the conversion is defined in /src/backend/parser/scan.l:

/* Convert "!=" operator to "<>" for compatibility */
if (strcmp(yytext, "!=") == 0)
    yylval->str = pstrdup("<>");
else
    yylval->str = pstrdup(yytext);
return Op;

scan.l is used almost at the beginning of parsing. There is also note in Create Operator chapter:

The operator != is mapped to <> on input, so these two names are always equivalent.

You can find <> operator in pg_operator table, but there is nothing for !=:

select * from pg_operator where oprname = '<>';
select * from pg_operator where oprname = '!=';

There is a lot on this topic in this similar question.