Thread: doesn't recognize "!=-" (not equal to a negative value)
Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Seems that postgres has a problem parsing a "not equal negative value". Anybody know if this is fixed in later versions? Paul Tilles
Paul Tilles <Paul.Tilles@noaa.gov> writes:
> UPDATE tablename SET value = 0.0 where value!=-9.4;
> ERROR: operator does not exist: smallint !=- integer
> HINT: No operator matches the given name and argument type(s). You may
> need to add explicit type casts.
This is not a bug, this is a feature.
Put a space between, or else use the SQL-standard spelling of not-equals,
ie <>
UPDATE tablename SET value = 0.0 where value!= -9.4;
UPDATE tablename SET value = 0.0 where value<>-9.4;
regards, tom lane
On Tue, 2006-07-11 at 12:11, Paul Tilles wrote: > Version postgres 7.4.7: > > Following sql > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > results in the error message > > ERROR: operator does not exist: smallint !=- integer > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > > Seems that postgres has a problem parsing a "not equal negative value". > > Anybody know if this is fixed in later versions? Ummmm. technically, it's not broken. SQL spec says not equal is specified by: <> not != OTOH, if you put a space in there, it'd work. UPDATE tablename SET value = 0.0 where value != -9.4; should work.
On Jul 11, 2006, at 1:11 PM, Paul Tilles wrote: > ERROR: operator does not exist: smallint !=- integer > HINT: No operator matches the given name and argument type(s). You > may need to add explicit type casts. I'm pretty sure the SQL-standard spelling of "not equals" is "<>". Postgres supporting "!=" is just a nicety. In fact, the documentation for comparison operators states that "The != operator is converted to <> in the parser stage.". http://www.postgresql.org/ docs/8.1/static/functions-comparison.html Alternatively, you can put a space before the minus sign: UPDATE tablename SET value = 0.0 where value!= -9.4; I think the reason for what seems like a mis-parsing is due to Postgres' extensible operator system. Postgres can't disambiguate what you mean by "!=-" because those three characters are also valid in custom operators. See http://www.postgresql.org/docs/8.1/static/ sql-createoperator.html for the complete list of valid characters. eric
From a brief and similar session (below), perhaps the best solution is to
simply insert a space between the '=' and the '-'??
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.
TJHart=# select 0.0 != -9.4;
?column?
----------
t
(1 row)
TJHart=# select 0.0 !=-9.4;
ERROR: operator does not exist: numeric !=- numeric
HINT: No operator matches the given name and argument type(s). You may need
to add explicit type casts.
TJHart=#
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Paul Tilles
Sent: Tuesday, July 11, 2006 12:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] doesn't recognize "!=-" (not equal to a negative value)
Version postgres 7.4.7:
Following sql
UPDATE tablename SET value = 0.0 where value!=-9.4;
results in the error message
ERROR: operator does not exist: smallint !=- integer
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
Seems that postgres has a problem parsing a "not equal negative value".
Anybody know if this is fixed in later versions?
Paul Tilles
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Yes. That works. I think that the parser should work properly either way. Paul Bruce Momjian wrote: > Paul Tilles wrote: > >> Version postgres 7.4.7: >> >> Following sql >> >> UPDATE tablename SET value = 0.0 where value!=-9.4; >> >> results in the error message >> >> ERROR: operator does not exist: smallint !=- integer >> HINT: No operator matches the given name and argument type(s). You may >> need to add explicit type casts. >> > > Have you tried? > > value != -9.4 > > --------------------------------------------------------------------------- > > >> Seems that postgres has a problem parsing a "not equal negative value". >> >> Anybody know if this is fixed in later versions? >> >> Paul Tilles >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> > >
Paul Tilles wrote: > Version postgres 7.4.7: > > Following sql > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > results in the error message > > ERROR: operator does not exist: smallint !=- integer > HINT: No operator matches the given name and argument type(s). You > may need to add explicit type casts. > > Seems that postgres has a problem parsing a "not equal negative value". > > Anybody know if this is fixed in later versions? > > Paul Tilles > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org Just tested on 8.1.1 I was getting the same error but if i put a space between the "=" and the "-" it works! Oisin
Paul Tilles wrote: > Version postgres 7.4.7: > > Following sql > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > results in the error message > > ERROR: operator does not exist: smallint !=- integer > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. Have you tried? value != -9.4 --------------------------------------------------------------------------- > > Seems that postgres has a problem parsing a "not equal negative value". > > Anybody know if this is fixed in later versions? > > Paul Tilles > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Tue, Jul 11, 2006 at 13:11:16 -0400, Paul Tilles <Paul.Tilles@noaa.gov> wrote: > Version postgres 7.4.7: > > Following sql > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > results in the error message > > ERROR: operator does not exist: smallint !=- integer > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > > Seems that postgres has a problem parsing a "not equal negative value". > > Anybody know if this is fixed in later versions? I don't think this is a bug. Postgres allows for user defined operators and !=- looks like one operator rather than two. Putting a space between = and - will fix the problem. For example: bruno=> select 1 != -1; ?column? ---------- t (1 row) bruno=> select 1 !=-1; ERROR: operator does not exist: integer !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
2006/7/11, Oisin Glynn <me@oisinglynn.com>: > Paul Tilles wrote: > > Version postgres 7.4.7: > > > > Following sql > > > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > > > results in the error message > > > > ERROR: operator does not exist: smallint !=- integer > > HINT: No operator matches the given name and argument type(s). You > > may need to add explicit type casts. > > > > Seems that postgres has a problem parsing a "not equal negative value". > > > > Anybody know if this is fixed in later versions? > > > > Paul Tilles > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > Just tested on 8.1.1 I was getting the same error but if i put a space > between the "=" and the "-" it works! I have already counted 6 very similar answers. Is there some problem with the list software that prevents people from noticing it has already been answered? Regards, Clodoaldo Pinto
On Tue, Jul 11, 2006 at 04:26:41PM -0400, Paul Tilles wrote: > Yes. That works. I think that the parser should work properly either way. You're mistaken. PostgreSQL by design makes it possible for you to define a custom operator like !=- and use it. > > Paul > > Bruce Momjian wrote: > >Paul Tilles wrote: > > > >>Version postgres 7.4.7: > >> > >>Following sql > >> > >>UPDATE tablename SET value = 0.0 where value!=-9.4; > >> > >>results in the error message > >> > >>ERROR: operator does not exist: smallint !=- integer > >>HINT: No operator matches the given name and argument type(s). You may > >>need to add explicit type casts. > >> > > > >Have you tried? > > > >value != -9.4 > > > >--------------------------------------------------------------------------- > > > > > >>Seems that postgres has a problem parsing a "not equal negative value". > >> > >>Anybody know if this is fixed in later versions? > >> > >>Paul Tilles > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Have you searched our list archives? > >> > >> http://archives.postgresql.org > >> > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On Wed, 2006-07-12 at 05:55, Clodoaldo Pinto wrote: > 2006/7/11, Oisin Glynn <me@oisinglynn.com>: > > Paul Tilles wrote: > > > Version postgres 7.4.7: > > > > > > Following sql > > > > > > UPDATE tablename SET value = 0.0 where value!=-9.4; > > > > > > results in the error message > > > > > > ERROR: operator does not exist: smallint !=- integer > > > HINT: No operator matches the given name and argument type(s). You > > > may need to add explicit type casts. > > > > > > Seems that postgres has a problem parsing a "not equal negative value". > > > > > > Anybody know if this is fixed in later versions? > > > > > > Paul Tilles > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > Just tested on 8.1.1 I was getting the same error but if i put a space > > between the "=" and the "-" it works! > > I have already counted 6 very similar answers. Is there some problem > with the list software that prevents people from noticing it has > already been answered? I think it's just the vagaries of the internet. By the time any one of us saw the other's response, we'd already written it. Plus, it's an easy question to answer, so there was little delay in thinking up a response for most folks. It's better than some other mailing lists I've been on, where such questions receive thundering silence for days on end... :)
On Wed, Jul 12, 2006 at 07:55:49 -0300, Clodoaldo Pinto <clodoaldo.pinto@gmail.com> wrote: > > I have already counted 6 very similar answers. Is there some problem > with the list software that prevents people from noticing it has > already been answered? In my case, my mail server had been heavily loaded over the last few days and I hadn't received the other replies yet when I sent mine. In the past the list servers had been slow to push stuff out, but I don't that has been much of an issue recently.
Scott Marlowe wrote: > On Wed, 2006-07-12 at 05:55, Clodoaldo Pinto wrote: > >> 2006/7/11, Oisin Glynn <me@oisinglynn.com>: >> >>> Paul Tilles wrote: >>> >>>> Version postgres 7.4.7: >>>> >>>> Following sql >>>> >>>> UPDATE tablename SET value = 0.0 where value!=-9.4; >>>> >>>> results in the error message >>>> >>>> ERROR: operator does not exist: smallint !=- integer >>>> HINT: No operator matches the given name and argument type(s). You >>>> may need to add explicit type casts. >>>> >>>> Seems that postgres has a problem parsing a "not equal negative value". >>>> >>>> Anybody know if this is fixed in later versions? >>>> >>>> Paul Tilles >>>> >>>> >>>> ---------------------------(end of broadcast)--------------------------- >>>> TIP 4: Have you searched our list archives? >>>> >>>> http://archives.postgresql.org >>>> >>> Just tested on 8.1.1 I was getting the same error but if i put a space >>> between the "=" and the "-" it works! >>> >> I have already counted 6 very similar answers. Is there some problem >> with the list software that prevents people from noticing it has >> already been answered? >> > > I think it's just the vagaries of the internet. By the time any one of > us saw the other's response, we'd already written it. Plus, it's an > easy question to answer, so there was little delay in thinking up a > response for most folks. > > It's better than some other mailing lists I've been on, where such > questions receive thundering silence for days on end... :) > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > I was somewhat confused it took my response about 5 hours to appear. I looked at the headers when my post eventually appeared it was odd. Other times the posts seem very quick. I seem to think that almost all the responses took a long time to appear. Oisin
On 7/11/06, Paul Tilles <Paul.Tilles@noaa.gov> wrote:
it works properly. just the proper way of functioning is not the one you would like to have.
you can simply add this operator:
CREATE FUNCTION not_equals_minus(int8, int8) RETURNS bool AS $BODY$
SELECT $1 <> -$2;
$BODY$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OPERATOR !=- (
leftarg = int8,
rightarg = int8,
procedure = not_equals_minus,
commutator = !=-
);
and then:
> select 1!=-2;
?column?
----------
t
(1 row)
depesz
Yes. That works. I think that the parser should work properly either way.
it works properly. just the proper way of functioning is not the one you would like to have.
you can simply add this operator:
CREATE FUNCTION not_equals_minus(int8, int8) RETURNS bool AS $BODY$
SELECT $1 <> -$2;
$BODY$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OPERATOR !=- (
leftarg = int8,
rightarg = int8,
procedure = not_equals_minus,
commutator = !=-
);
and then:
> select 1!=-2;
?column?
----------
t
(1 row)
depesz