On Wed, 2021-05-26 at 17:15 +0800, 张元超 wrote: > I encountered a problem when using PostgreSQL's comparison operators. The problem is as follows: > Problem Description: > When I use the comparison operator "!=" as the query condition, such as "select * from t1 where c1 !=-1", the database returns an error: "!=-operator does not exist". Because there is no space > between ‘=’ and ‘-’, if you enter a space between them, the sql can be executed normally. Therefore, although we can make sql execute normally by adding spaces, its behavior is different from other > comparison operators (such as ">,<,>=,<=,=,<>"). Other comparisons Operators will not have such problems. > > I guess that this should be because the database did not correctly handle the "!=" operator during sql parsing, so I think this should be a bug. This problem exists in the 11, 12, and 13 versions of > PostgreSQL. > > At the same time, I tried other databases, such as Oracle, but did not find the same problem. > Looking forward to your reply. See this comment in "scan.l": /* * For SQL compatibility, '+' and '-' cannot be the * last char of a multi-char operator unless the operator * contains chars that are not in SQL operators. * The idea is to lex '=-' as two operators, but not * to forbid operator names like '?-' that could not be * sequences of SQL operators. */ That means that a training minus is only treated as not belonging to the operator name if the preceding characters belong to a standard SQL operator name. Now "<" and ">" are standard operator characters, so "<>-" is treated as two tokens. But "!" does not appear in SQL standard operators, so there is no special processing. This is a hack to allow constructs like 1<>-2, which are required to comply with the SQL standard. If you want this behavior, sitch to standard SQL operator names and don't use !=. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
