Not equal to string value not working on postgreSQL

Question

I've written a very simple query

select *, "Status" from "ABC" WHERE "ABCId" = 7; // Got 100 rows

ABC table has some columns one of them is "Status" with null value usually appearing as (Null)

When I try these queries

-- 1
select * FROM "ABC" where "ABCId" = 7 AND "Status" <> 'success'

-- 2
select * FROM "ABC" where "ABCId" = 7 AND "Status" != 'success'

-- 3
select * FROM "ABC" where "ABCId" = 7 AND "Status" NOT ILIKE '%success%'

I'm not getting any rows, I bang my head, this is a simple query :/

No some times it might have the value 'failed' in that case, I should consider null & failed rows too — Arjun
– Arjun, Commented Jul 28, 2019 at 6:57
Then use an OR: AND status IS NULL OR status <> 'success' — JB Nizet
– JB Nizet, Commented Jul 28, 2019 at 6:58
what's the wrong with my query it is the basically same right? — Arjun
– Arjun, Commented Jul 28, 2019 at 7:01
Well, doesn't your experiment show you what the difference is? null is neither = nor <> to anything. tutorialspoint.com/sql/sql-null-values — JB Nizet
– JB Nizet, Commented Jul 28, 2019 at 7:03

score 13 · Accepted Answer · 2019-07-28 08:19:46Z

13

You can use the null safe comparison operator is distinct from:

and "Status" is distinct from 'success'

that will be true for null values as well.

edited Jul 28, 2019 at 8:19

answered Jul 28, 2019 at 8:10

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Arjun Over a year ago

Working, Alternative -> select * FROM "ABC" where "ABCId" = 7 AND COALESCE("Status", '') <> 'success'

justsomeguy · Accepted Answer · 2019-07-28 07:36:39Z

0

Give coalesce a try

select "ABCId", coalesce(status, 'unknown') from "ABC" where "ABCId" = 7 and ("Status" != 'success' OR status IS NULL);```

answered Jul 28, 2019 at 7:36

justsomeguy

5795 silver badges13 bronze badges

1 Comment

justsomeguy Over a year ago

@Arjun what number of rows do you get when you try ABCId=7 and Status='success' ? also what number for when you try ABCId=7 and Status='failure' ?

Collectives™ on Stack Overflow

Not equal to string value not working on postgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related