1

I am having a hard time understanding why the following query is not working?

select id,sold_dealer_id from myi_corporate where sold_dealer_id != 36;

When I do the following I am getting the result

select id,sold_dealer_id from myi_corporate where sold_dealer_id = 36;

However for != I am not getting the opposite results.

I am returning to psql after a long time after using MongoDB and might have forgotten the basics. Any suggestion is much appreciated

  • 4
    Any NULL values in that column? You might want to try where sold_dealer_id != 36 or sol_dealer_id is null` - or simplified to where sold_dealer_id is distinct from 36 – a_horse_with_no_name Nov 11 '19 at 16:22
  • all the remaining values are NULL, i just updated one row to have value '36' – Sijan Shrestha Nov 11 '19 at 16:25
  • Using IS NULL solves the problem, but I am trying to get values that are not != 36, any suggestion is much appreciated, but i now know the cause is NULL Thanks :) – Sijan Shrestha Nov 11 '19 at 16:32
  • Thanks a lot select * from myi_corporate where sold_dealer_id is distinct from 36 got the query worrking :) – Sijan Shrestha Nov 11 '19 at 16:41
  • Remember that SQL has tristate logic: true, false, and null. Use is null or is distinct from when comparing against null. – Colin 't Hart Nov 11 '19 at 18:25
3

You can use != rather than the SQL standard operator <>, but I recommend using the latter. That has no influence on your problem though.

Your table must contain some NULL values in sold_dealer_id.

Now NULL = 36 is not true, but NULL <> 36 is also not true, so such lines are excluded from both query results.

You can use sold_dealer_id IS DISTINCT FROM 36 to get the opposite of sold_dealer_id = 36. That operator will treat NULL values as if they were normal values.

  • Thanks for pointing this out! Yes the comparison is with NULL = 36 currently and seems fair, is there a correct way to compare with NULL as my query depends on using an id and want to fetch other rows that are sold_dealer_id != <NUMBERr> – Sijan Shrestha Nov 11 '19 at 16:35
  • I have added a way to do that to the answer. – Laurenz Albe Nov 11 '19 at 16:50
  • You can also add or sold_dealer IS NULL. – eckes Nov 11 '19 at 18:10
2

You are getting the results because a null value will never match anything else. If you know you are going to have null values I would also exclude those from the result.

select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id = 36
and    sold_dealer_id is not null;


select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id != 36
and    sold_dealer_id is not null;

You can also do a quick search to find any with a null value

select id,
       sold_dealer_id 
from   myi_corporate 
where  sold_dealer_id is null;
-3

By using correct postgres-Syntax...

select id,sold_dealer_id from myi_corporate where NOT (sold_dealer_id = 36);

Postgres is a database, not a c-family programming language

https://www.techonthenet.com/postgresql/not.php with some examples

  • I tried that, did not work! Thanks though :). I am getting 195 result for a select query but no luck with NOT or != – Sijan Shrestha Nov 11 '19 at 16:10
  • FYI, it is a foriegn key, does it make a difference ? – Sijan Shrestha Nov 11 '19 at 16:13
  • Okay turns out something I am doing is wrong, I tried with a string field example select * from myi_corporate where NOT (company_reg_id = '9990000000000'); which seems to work but for that field which is an integer and a foreign key its not working , any solutions? – Sijan Shrestha Nov 11 '19 at 16:19
  • 4
    Although the SQL standard defines <> as the "not equals" operator, Postgres does support using != as well. Changing the condition to a negated equals condition won't change a thing. NOT (sold_dealer_id = 36) is the same as sold_dealer_id <> 36 which is the same as (sold_dealer_id != 36 – a_horse_with_no_name Nov 11 '19 at 16:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.