Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZcjvI-0006Lt-Re for pgsql-admin@arkaria.postgresql.org; Fri, 18 Sep 2015 00:53:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZcjvH-0001Fg-NI for pgsql-admin@arkaria.postgresql.org; Fri, 18 Sep 2015 00:53:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZcjvH-0001FW-3v for pgsql-admin@postgresql.org; Fri, 18 Sep 2015 00:53:07 +0000 Received: from mail-wi0-x236.google.com ([2a00:1450:400c:c05::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZcjvE-00068o-Es for pgsql-admin@postgresql.org; Fri, 18 Sep 2015 00:53:05 +0000 Received: by wiclk2 with SMTP id lk2so44718377wic.0 for ; Thu, 17 Sep 2015 17:53:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to:content-type; bh=+8JU+Fl/hJhNFxjHlnFRs/KRprauY8rOAw3TuTEVp7o=; b=CovgmiLzsMpxw5FYeFyii9V4ff77gJVDXk38b1ny4mRy7uFV8E25ZotKbrm3rw6tBU 0wPAN2cjOvbMTjPWqtNUeeV1hzxIdxST6Tc0eehfiyecoAkBladqG9K3zt//CUstQk2F rqqd3dgmHg8mkCmzS3kk4QBC3zaNmGxcPs9aB8xYewM0ZrhbrPD+61zmzU6vAzTxS4gh 1k754MMNDNZIk6FNudSXkkcPUZL1KF24RbxOiHl7/982Mt6NT/ZhkzBmMUxfKAyUFLQU tS5NDaLs8E5Db606THSD/jgCt7hBhc950Xw9IYlkq59hQl0gI2AZaCTW7vYOgTC4D4qb iHGA== X-Received: by 10.194.133.70 with SMTP id pa6mr3776571wjb.48.1442537582084; Thu, 17 Sep 2015 17:53:02 -0700 (PDT) Received: from ?IPv6:2605:6000:f686:8500:2c3a:5d1b:9770:4776? ([2605:6000:f686:8500:2c3a:5d1b:9770:4776]) by smtp.gmail.com with ESMTPSA id wj4sm6038981wjb.10.2015.09.17.17.53.00 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 17 Sep 2015 17:53:01 -0700 (PDT) Subject: Re: string not equal query, postgresql 9.4.4 To: "David G. Johnston" References: <55FA13F1.7060204@gmail.com> <55FB3C42.6030301@gmail.com> Cc: Albe Laurenz , "pgsql-admin@postgresql.org" From: John Scalia Message-ID: <55FB6069.3030108@gmail.com> Date: Thu, 17 Sep 2015 19:52:57 -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: Content-Type: multipart/alternative; boundary="------------080709030209000009040509" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------080709030209000009040509 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit On 9/17/2015 6:49 PM, David G. Johnston wrote: > On Thu, Sep 17, 2015 at 6:18 PM, John Scalia >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.​ > I tried your code above, and some additional queries prior to your message arriving, and I see now why I'm still getting multiple row beyond my original expecting. Carefully looking at the rows being produced showed that each instrument is performing a powerup test and all those passed. I guess I was just expecting too much from distinct and I understand why. I just need to add a second where clause if I can figure out how to make the powerup test clearly indicated as such in the table. Thanks for everyone's help, I should have examined the automated data collection more closely. -- Jay --------------080709030209000009040509 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 9/17/2015 6:49 PM, David G. Johnston wrote:
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.​
 
I tried your code above, and some additional queries prior to your message arriving, and I see now why I'm still getting multiple row beyond my original expecting. Carefully looking at the rows being produced showed that each instrument is performing a powerup test and all those passed. I guess I was just expecting too much from distinct and I understand why. I just need to add a second where clause if I can figure out how to make the powerup test clearly indicated as such in the table.

Thanks for everyone's help, I should have examined the automated data collection more closely.
--
Jay
--------------080709030209000009040509--