pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feeddifferences between <> and != when using signed values on the right hand side
3+ messages / 3 participants
[nested] [flat]
* differences between <> and != when using signed values on the right hand side
@ 2024-05-16 09:27 David Hunnisett <david.hunnisett@probit.io>
0 siblings, 1 reply; 3+ messages in thread
From: David Hunnisett @ 2024-05-16 09:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: differences between <> and != when using signed values on the right hand side
@ 2024-05-16 14:39 David G. Johnston <david.g.johnston@gmail.com>
parent: David Hunnisett <david.hunnisett@probit.io>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-05-16 14:39 UTC (permalink / raw)
To: David Hunnisett <david.hunnisett@probit.io>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
In short, a consequence of allowing users to define their own operators.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: differences between <> and != when using signed values on the right hand side
@ 2024-05-16 16:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-05-16 16:34 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: David Hunnisett <david.hunnisett@probit.io>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-05-16 16:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-16 09:27 differences between <> and != when using signed values on the right hand side David Hunnisett <david.hunnisett@probit.io>
2024-05-16 14:39 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-16 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox