pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstring not equal query, postgresql 9.4.4
8+ messages / 3 participants
[nested] [flat]
* string not equal query, postgresql 9.4.4
@ 2015-09-17 01:14 John Scalia <jayknowsunix@gmail.com>
2015-09-17 02:23 ` Re: string not equal query, postgresql 9.4.4 David G. Johnston <david.g.johnston@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 8+ messages in thread
From: John Scalia @ 2015-09-17 01:14 UTC (permalink / raw)
To: pgsql-admin
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 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?
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
@ 2015-09-17 02:23 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2015-09-17 02:23 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Wed, Sep 16, 2015 at 9:14 PM, John Scalia <jayknowsunix@gmail.com> wrote:
> 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 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?
> --
> Jay
>
WITH vals (v) AS (
VALUES ('PASS'::char(4)), ('FAIL'::char(4))
)
SELECT
DISTINCT
*
FROM vals
WHERE v <> 'PASS'::char(4);
Since this is basically what you did you apparently either do not
understand your data completely or you have failed to convey necessary
information to the audience whom you are asking for help.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
@ 2015-09-17 08:21 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Albe Laurenz @ 2015-09-17 08:21 UTC (permalink / raw)
To: 'John Scalia *EXTERN*' <jayknowsunix@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2015-09-17 12:18 ` John Scalia <jayknowsunix@gmail.com>
2015-09-17 22:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: John Scalia @ 2015-09-17 12:18 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
@ 2015-09-17 22:18 ` John Scalia <jayknowsunix@gmail.com>
2015-09-17 23:49 ` Re: string not equal query, postgresql 9.4.4 David G. Johnston <david.g.johnston@gmail.com>
2015-09-18 07:35 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 8+ messages in thread
From: John Scalia @ 2015-09-17 22:18 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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 should have seen the correct output. So, any ideas?
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 22:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
@ 2015-09-17 23:49 ` David G. Johnston <david.g.johnston@gmail.com>
2015-09-18 00:52 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-09-17 23:49 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 22:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 23:49 ` Re: string not equal query, postgresql 9.4.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2015-09-18 00:52 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: John Scalia @ 2015-09-18 00:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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 <mailto: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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: string not equal query, postgresql 9.4.4
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 08:21 ` Re: string not equal query, postgresql 9.4.4 Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 22:18 ` Re: string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
@ 2015-09-18 07:35 ` Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 0 replies; 8+ messages in thread
From: Albe Laurenz @ 2015-09-18 07:35 UTC (permalink / raw)
To: 'John Scalia *EXTERN*' <jayknowsunix@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-09-18 07:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-17 01:14 string not equal query, postgresql 9.4.4 John Scalia <jayknowsunix@gmail.com>
2015-09-17 02:23 ` David G. Johnston <david.g.johnston@gmail.com>
2015-09-17 08:21 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-09-17 12:18 ` John Scalia <jayknowsunix@gmail.com>
2015-09-17 22:18 ` John Scalia <jayknowsunix@gmail.com>
2015-09-17 23:49 ` David G. Johnston <david.g.johnston@gmail.com>
2015-09-18 00:52 ` John Scalia <jayknowsunix@gmail.com>
2015-09-18 07:35 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox