Subject: Re: string not equal query, postgresql 9.4.4
To: Albe Laurenz <laurenz.albe@wien.gv.at>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
References: <55FA13F1.7060204@gmail.com>
 <A737B7A37273E048B164557ADEF4A58B50FA82A0@ntex2010i.host.magwien.gv.at>
 <CABzCKRAdrgi+feKz6nCzNx_4P7xDoJ-G+pL3UQ4f9+C+VtGQGQ@mail.gmail.com>
 <A737B7A37273E048B164557ADEF4A58B50FA85B6@ntex2010i.host.magwien.gv.at>
From: John Scalia <jayknowsunix@gmail.com>
Message-ID: <55FB3C42.6030301@gmail.com>
Date: Thu, 17 Sep 2015 17:18:42 -0500
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: 
 <A737B7A37273E048B164557ADEF4A58B50FA85B6@ntex2010i.host.magwien.gv.at>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On 9/17/2015 7:54 AM, Albe Laurenz wrote:
> John Scalia wrote:
>>>> Having a bit of a head-scratching problem. I'm writing a query where I=
 want to output only distinct
>>>> rows where one char(4) field does not equal a specific value. Somethin=
g like:
>>>>
>>>> select distinct testname where result <> 'PASS";      #i.e., only the =
FAIL or WARN tests
>>>>
>>>> I've tried several different variants like "!~", "not like ('PASS')", =
"is distinct from 'PASS'", and
>>>> so forth, but obviously I'm missing something as every row is being ou=
tput not
>>>> just the ones which do not equal PASS. What would be the correct synta=
x for this in a 9.4.4 database?
>>> Could it be that there are spaces or other invisible characters in the =
"result" attribute?
>> I'm not in front of that server at the moment, so I can't test anything =
else, but I should have
>> explained that the result field is char(4) not null, and all the values =
in it are length(4), so no
>> padding should exist. I'll try some of these once I get back home today,=
 but what had me concerned is
>> if I entered result =3D 'PASS', as opposed to anything like not equal, t=
he query worked as expected, and
>> Albe, I believe the wrong syntax was just my typo from reentering it for=
 the email. Also, David, the
>> reason I was I searching for anything other than the value 'PASS' is the=
 field has values of 'FAIL' or
>> a 4 digit hex code produced by the instrumentation generating the data. =
I sure didn't want to have to
>> list every possible 4 digit hex value that is a possibility. Sorry I was=
n't very clear in my first
>> posting. I had been struggling with this for some time, and my temper wa=
s a little short.
> Well, it works for me:
>
> test=3D> CREATE TABLE test (id integer PRIMARY KEY, val character(4));
> CREATE TABLE
> test=3D> INSERT INTO test VALUES (1, 'PASS');
> INSERT 0 1
> test=3D> INSERT INTO test VALUES (2, 'FAIL');
> INSERT 0 1
> test=3D> INSERT INTO test VALUES (3, 'PASS');
> INSERT 0 1
> test=3D> INSERT INTO test VALUES (4, 'STHG');
> INSERT 0 1
> test=3D> SELECT * FROM test WHERE val <> 'PASS';
>  id | val
> ----+------
>   2 | FAIL
>   4 | STHG
> (2 rows)
>
> Please double check your data.
> Are they all ASCII? Any chance that there might be weird characters?
>
> Yours,
> Laurenz Albe
Thanks Laurenz,

I'm believing something here may be something incorrect with my setup, but =
here's the output from \d+ results

Table "public.results"
Column       |                 Type               | Modifiers | Storage    =
 | Stats target | Description
instrument | character varying(10)  | not null     | extended |            =
           |
date             | character varying(15)  | not null     | extended |      =
                 |
result           | character(4)                  | not null     | extended =
|                       |

The table has six rows:

Instrument |        date        | result
-----------------+-------------------+----------
visc100        | 01/01/2015 | PASS
visc60          | 01/01/2015 | FAIL
visc60pre    | 01/01/2015 | 6FB3
visc60post  | 01/01/2015 | 7F5A
density        | 01/01/2015 | PASS
base             | 01/01/2015 | PASS

The last query I ran was:

SELECT * FROM results where result <> 'PASS';

and it produced all the rows, not just the ones beginning with visc60 that =
I expected. Based on what you had written, I should have seen the correct o=
utput. So, any ideas?
--
Jay


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin