Обсуждение: string not equal query, postgresql 9.4.4

Hi all,

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
obviouslyI'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?
--
Jay

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?

I observe that your SQL query is syntactically wrong (closing double quote)
and the FROM clause is missing, but apart from that it should work fine.

Could it be that there are spaces or other invisible characters in the "result" attribute?

What do you get for
  SELECT DISTINCT '|' || result || '|' FROM <table> WHERE result <> 'PASS';

Yours,
Laurenz Albe

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

On Thu, Sep 17, 2015 at 6:18 PM, John Scalia <jayknowsunix@gmail.com> wrote:

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 output. So, any ideas?

​ EXPLAIN ANALYZE SELECT * FROM results WHERE result <> 'PASS';

What happens when you run the following?  Do you see the same incorrect behavior?

WITH vals (v) AS (

VALUES ('PASS'::char(4)), ('FAIL'::char(4))

)

SELECT 

​ DISTINCT ​

*

FROM vals

WHERE v <> 'PASS'::char(4);

David J.​

 

John Scalia wrote:
> 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 output. So, any ideas?

Not really...

What does the execution plan look like:
  EXPLAIN (VERBOSE, COSTS off) SELECT * FROM results where result <> 'PASS';

What do you get for
  SHOW lc_collate;
  SHOW lc_ctype;
Maybe some problem with your locale...

Yours,
Laurenz Albe