Обсуждение: != should give error?
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)
postgres=# select * from tbl where col_a = 1;
col_a | col_b
-------+-------
1 | 2
(1 row)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
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)
postgres=# select * from tbl where col_a = 1;
col_a | col_b
-------+-------
1 | 2
(1 row)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
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)
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