Re: string not equal query, postgresql 9.4.4

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. Something 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 output not
>>>> just the ones which do not equal PASS. What would be the correct syntax 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 = 'PASS', as opposed to anything like not equal, the 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 wasn't very clear in my first
>> posting. I had been struggling with this for some time, and my temper was a little short.
> Well, it works for me:
>
> test=> CREATE TABLE test (id integer PRIMARY KEY, val character(4));
> CREATE TABLE
> test=> INSERT INTO test VALUES (1, 'PASS');
> INSERT 0 1
> test=> INSERT INTO test VALUES (2, 'FAIL');
> INSERT 0 1
> test=> INSERT INTO test VALUES (3, 'PASS');
> INSERT 0 1
> test=> INSERT INTO test VALUES (4, 'STHG');
> INSERT 0 1
> test=> 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
shouldhave seen the correct output. So, any ideas? 
--
Jay