Thread: numeric fields and null
Im trying to pull out all records in a table where field 'postaladdressid=null' but I cant figure this out. \d users .... id | numeric postaladdressid | numeric .... adhoc=# update users set postaladdressid=null where id=50; UPDATE 1 adhoc=# select id,postaladdressid from users where postaladdressid=null;id | postaladdressid ----+----------------- (0 rows) Why did it not select record 50? just for your info, this is what record 50 looks like. adhoc=# select id,postaladdressid from users where id=50;id | postaladdressid ----+-----------------50 | (1 row) Don -- ** irc: irc.freenode.net nick: don-o channels: #wireless, #java ** As we enjoy great advantages from inventions of others, we should be ** glad of an opportunity to serve others by any invention of ours; and ** this we should do freely and generously. --Benjamin Franklin
Use: SELECT id,postaladdressid FROM users WHERE postaladdressid IS NULL; Don Park wrote: >Im trying to pull out all records in a table where field >'postaladdressid=null' but I cant figure this out. > >\d users >.... >id | numeric >postaladdressid | numeric >.... > >adhoc=# update users set postaladdressid=null where id=50; >UPDATE 1 > >adhoc=# select id,postaladdressid from users where postaladdressid=null; > id | postaladdressid >----+----------------- >(0 rows) > >Why did it not select record 50? > >just for your info, this is what record 50 looks like. >adhoc=# select id,postaladdressid from users where id=50; > id | postaladdressid >----+----------------- > 50 | >(1 row) > > >Don > >
On Thu, 22 May 2003, Don Park wrote: > Im trying to pull out all records in a table where field > 'postaladdressid=null' but I cant figure this out. > > \d users > .... > id | numeric > postaladdressid | numeric > .... > > adhoc=# update users set postaladdressid=null where id=50; > UPDATE 1 > > adhoc=# select id,postaladdressid from users where postaladdressid=null; > id | postaladdressid > ----+----------------- > (0 rows) > > Why did it not select record 50? Because NULL=NULL is not true (it's unknown). Use IS NULL if you want to see if something is NULL.
the correct way to do it is select id,postaladdressid from users where postaladdressid IS null; I could tell you way, but this explanation (http://techdocs.postgresql.org/guides/BriefGuideToNulls) is so good that I'would waste your time :) On Thursday 22 May 2003 17:17, Don Park wrote: > Im trying to pull out all records in a table where field > 'postaladdressid=null' but I cant figure this out. > > \d users > .... > id | numeric > postaladdressid | numeric > .... > > adhoc=# update users set postaladdressid=null where id=50; > UPDATE 1 > > adhoc=# select id,postaladdressid from users where postaladdressid=null; > id | postaladdressid > ----+----------------- > (0 rows) > > Why did it not select record 50? > > just for your info, this is what record 50 looks like. > adhoc=# select id,postaladdressid from users where id=50; > id | postaladdressid > ----+----------------- > 50 | > (1 row) > > > Don
thank you. that sounds like just the info I need. Don On Thu, 22 May 2003, Franco Bruno Borghesi wrote: > the correct way to do it is > select id,postaladdressid from users where postaladdressid IS null; > > I could tell you way, but this explanation (http://techdocs.postgresql.org/guides/BriefGuideToNulls) is > so good that I'would waste your time :) > > On Thursday 22 May 2003 17:17, Don Park wrote: > > Im trying to pull out all records in a table where field > > 'postaladdressid=null' but I cant figure this out. > > > > \d users > > .... > > id | numeric > > postaladdressid | numeric > > .... > > > > adhoc=# update users set postaladdressid=null where id=50; > > UPDATE 1 > > > > adhoc=# select id,postaladdressid from users where postaladdressid=null; > > id | postaladdressid > > ----+----------------- > > (0 rows) > > > > Why did it not select record 50? > > > > just for your info, this is what record 50 looks like. > > adhoc=# select id,postaladdressid from users where id=50; > > id | postaladdressid > > ----+----------------- > > 50 | > > (1 row) > > > > > > Don > > [lucifer.akyasociados.com.ar -4m -4s klickitat.st -1m -21s ] > -- ** irc: irc.freenode.net nick: don-o channels: #wireless, #java ** As we enjoy great advantages from inventions of others, we should be ** glad of an opportunity to serve others by any invention of ours; and ** this we should do freely and generously. --Benjamin Franklin
> > adhoc=# update users set postaladdressid=null where id=50; > > UPDATE 1 > > > > adhoc=# select id,postaladdressid from users where postaladdressid=null; > > id | postaladdressid > > ----+----------------- > > (0 rows) > > > > Why did it not select record 50? > > Because NULL=NULL is not true (it's unknown). Use IS NULL if you want to > see if something is NULL. If in postgresql.conf set parameter transform_null_equals=TRUE ????