Postgres Pro
Downloads
Home   >   mailing lists

Re: string not equal query, postgresql 9.4.4 - Mailing list pgsql-admin

From John Scalia
Subject Re: string not equal query, postgresql 9.4.4
Date
Msg-id CABzCKRAdrgi+feKz6nCzNx_4P7xDoJ-G+pL3UQ4f9+C+VtGQGQ@mail.gmail.com
Whole thread Raw
In response to Re: string not equal query, postgresql 9.4.4  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-admin
Tree view
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.

On Thu, Sep 17, 2015 at 3:21 AM, Albe Laurenz <laurenz.albe@wien.gv.at> 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?

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

pgsql-admin by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: string not equal query, postgresql 9.4.4
Next
From: John Scalia
Date:
Subject: Re: string not equal query, postgresql 9.4.4