Re: 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

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.

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
>
>

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 ????