!<space>= should give error?

On 21 December 2017 at 14:43, Rajkumar Raghuwanshi <
rajkumar.raghuwanshi@enterprisedb.com> wrote:

Hi,

with below query I am getting unexpected output. here !<space>= is
behaving as =
is this expected behaviour?

postgres=# create table tbl (col_a int, col_b int);
CREATE TABLE
postgres=# insert into tbl values (1,2);
INSERT 0 1
postgres=# insert into tbl values (2,1);
INSERT 0 1

*postgres=# select * from tbl where col_a ! = 1; col_a |
col_b-------+------- 1 | 2(1 row)*

One-factorial is one, so it's correct.

test=> SELECT 1!, 2!, 3!;
?column? | ?column? | ?column?
----------+----------+----------
1 | 2 | 6
(1 row)

test=> SELECT (1 !) = 1;
?column?
----------
t
(1 row)

test=> \do+ !
List of operators
Schema | Name | Left arg type | Right arg type | Result type |
Function | Description
------------+------+---------------+----------------+-------------+-------------+-------------
pg_catalog | ! | bigint | | numeric |
numeric_fac | factorial
(1 row)

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2017-12-21 08:16, Craig Ringer wrote:

postgres=# create table tbl (col_a int, col_b int);
CREATE TABLE
postgres=# insert into tbl values (1,2);
INSERT 0 1
postgres=# insert into tbl values (2,1);
INSERT 0 1
*postgres=# select * from tbl where col_a ! = 1;
col_a | col_b
-------+-------
1 | 2
(1 row)*
*
*

One-factorial is one, so it's correct.

Yes, but I think he was expecting the behavior to be different from /
not equal. So he meant to write:

select * from tbl where col_a != 1;

I.e. col_a different from 1.

BTW isn't the "preferred" way of writing different from / not equal in
SQL to use the <> operator?

/Mikael

Mikael Kjellstr�m wrote:

BTW isn't the "preferred" way of writing different from / not equal in SQL
to use the <> operator?

Yes, <> is the operator mandated by the SQL standard. I don't think !=
appears anywhere -- it's our extension only and thus if you use it you
should not expect your applications to work on other DBMSs.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

Mikael Kjellström wrote:

BTW isn't the "preferred" way of writing different from / not equal in SQL
to use the <> operator?

Yes, <> is the operator mandated by the SQL standard. I don't think !=
appears anywhere -- it's our extension only and thus if you use it you
should not expect your applications to work on other DBMSs.

It seems to be supported work on all the DBMSs supported by sqlfiddle.com,
i.e. Oracle, MySQL, PostgreSQL, SQLite and MS SQL Server.

The DB2 documentation¹ says it's supported (it even supports ¬=, and
negating < and >, not just =).

Firebird² also supports it, in addition to ~= and ^= (and it also
supports negating < and >).

So while it might not be standard SQL, it's definitely not "our
extension only".

[1]: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/sqlp/rbafycompop.htm
[2]: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-commons-expressions.html#fblangref25-dtyp-tbl-comparpreced

- ilmari
--
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen

Dagfinn Ilmari Manns�ker wrote:

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

Yes, <> is the operator mandated by the SQL standard. I don't think !=
appears anywhere -- it's our extension only and thus if you use it you
should not expect your applications to work on other DBMSs.

So while it might not be standard SQL, it's definitely not "our
extension only".

Well, that means everybody else also extends, but since it's not
standard, the operator could mean something different and if your app
breaks because of that, you get to keep the pieces.

As in some other thread, "we're in {good company, bad society}" in this
regard.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services