pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsilly NULL question
3+ messages / 2 participants
[nested] [flat]
* silly NULL question
@ 2003-07-24 23:15 Dan Weeks <danimal@wildbrain.com>
2003-07-24 23:25 ` Re: silly NULL question Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Dan Weeks @ 2003-07-24 23:15 UTC (permalink / raw)
To: pgsql-sql
Howdy,
I'm sure most of you PostgreSQL users out there will be able to answer
this, but I'm just not finding the answer.
Let's say I have a table called project that has three fields:
field | type
-------------------
id int
name varchar(30)
parent_id int
right now there is only one row in the table:
id | name | parent_id
-----------------------------------
1 | silly |
You'll notice there is nothing in the parent_id as there is no parent for
this particular project. Previously (with PostgreSQL 7.1) a query of:
select * from project where name = 'silly' AND parent_id = NULL;
Would return the row. Now with an upgrade to PostgreSQL 7.3 (yes, I know
there are many changes and we're working through them right now) the same
query returns nothing. Dropping the "AND parent_id = NULL" returns the row
as expected.
Now, what is the correct specifier for an empty int value? Or, are there
suggestions for how to create that column (like using NULL as the default
maybe) that experts can clue-stick me with?
Thanks for your time. If this is the wrong list to ask these questions to
please point me in the right direction.
-dan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: silly NULL question
2003-07-24 23:15 silly NULL question Dan Weeks <danimal@wildbrain.com>
@ 2003-07-24 23:25 ` Rod Taylor <rbt@rbt.ca>
2003-07-24 23:31 ` Re: silly NULL question Dan Weeks <danimal@wildbrain.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rod Taylor @ 2003-07-24 23:25 UTC (permalink / raw)
To: Dan Weeks <danimal@wildbrain.com>; +Cc: pgsql-sql
> Would return the row. Now with an upgrade to PostgreSQL 7.3 (yes, I know
> there are many changes and we're working through them right now) the same
> query returns nothing. Dropping the "AND parent_id = NULL" returns the row
> as expected.
NULL is similar to UNKNOWN.
So, NULL = NULL is the similar to UNKNOWN = UNKNOWN. Since you don't
know it, how can you tell if they're equal or not?
Syntax you're looking for is: AND parent_id IS NULL
If you really really really need = NULL (due to some MS product which
ignores SQL standards -- say MS Access) there is a toggle in the
postgresql.conf file to allow automated conversion of = NULL to IS NULL
within the server.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: silly NULL question
2003-07-24 23:15 silly NULL question Dan Weeks <danimal@wildbrain.com>
2003-07-24 23:25 ` Re: silly NULL question Rod Taylor <rbt@rbt.ca>
@ 2003-07-24 23:31 ` Dan Weeks <danimal@wildbrain.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dan Weeks @ 2003-07-24 23:31 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
>>>>> "RT" == "Rod Taylor" <rbt@rbt.ca>:
RT> NULL is similar to UNKNOWN.
RT>
RT> So, NULL = NULL is the similar to UNKNOWN = UNKNOWN. Since you don't
RT> know it, how can you tell if they're equal or not?
RT>
RT> Syntax you're looking for is: AND parent_id IS NULL
ah, of course. Thanks Rod.
RT> If you really really really need = NULL (due to some MS product which
RT> ignores SQL standards -- say MS Access) there is a toggle in the
RT> postgresql.conf file to allow automated conversion of = NULL to IS NULL
RT> within the server.
ah ha! the culprit. I see this was default through 7.1. Thanks again for
accurate and quick reply.
-dan
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-24 23:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-24 23:15 silly NULL question Dan Weeks <danimal@wildbrain.com>
2003-07-24 23:25 ` Rod Taylor <rbt@rbt.ca>
2003-07-24 23:31 ` Dan Weeks <danimal@wildbrain.com>
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