Thread: selecting all records where a column is null
Hi, I am trying to select every record from a table where a specific
column does not have a value in it yet. Is there a way to express that
in a SQL statement? Here are the few I tried:
select * from table where field;
select * from table where field = '';
select * from table where field = NULL;
and some other stuff the is way more broken. I have flipped through the
docs a bit but I haven't found anything useful yet. If someone could
point me to the right place in the docs or give me an example of a
select that will do this I would be very grateful.
--Erik
Elmshauser, Erik wrote: >Hi, I am trying to select every record from a table where a specific >column does not have a value in it yet. Is there a way to express that >in a SQL statement? Here are the few I tried: > >select * from table where field; >select * from table where field = ''; >select * from table where field = NULL; > select * from table where field is null; is rather than =. Since null is undefined, it's not appropriate to test for equality to null. -Fran
On Thu, 20 Jun 2002 11:46:43 -0700, "Elmshauser, Erik" <erike@pbgnw.com> wrote: >select * from table where field = NULL; SELECT * FROM t WHERE field IS NULL; Servus Manfred
> -----Original Message----- > From: Elmshauser, Erik [mailto:erike@pbgnw.com] > Sent: 20 June 2002 19:47 > To: pgsql-general@postgresql.org > Subject: [GENERAL] selecting all records where a column is null > > > Hi, I am trying to select every record from a table where a > specific column does not have a value in it yet. Is there a > way to express that in a SQL statement? Here are the few I tried: > > select * from table where field; > select * from table where field = ''; > select * from table where field = NULL; select * from table where field IS NULL; Regards, Dave.
On Thu, Jun 20, 2002 at 11:46:43AM -0700, Elmshauser, Erik wrote:
> Hi, I am trying to select every record from a table where a specific
> column does not have a value in it yet.
What you are looking for is NULL, as you note. Your problem is this:
> select * from table where field = NULL;
To make an oversimplification, SQL uses a three-value logic: T,F, and
NULL. NULL is for unknown cases. So
NULL = NULL
is false, because the left side is unknown, and the right side
is unknown also. (It might be that if you knew the two values, they
would not be equivalent.) What you want is
SELECT * FROM table WHERE field IS NULL;
In 7.2.1, there is a switch that allows you to turn on "= NULL"
conversion, in order to support some old behaviour (I think to
support Access, but I forget). But unless you need it to support
some external program, don't use it. Use the IS NULL locution
instead, because it'll prevent you from getting snagged by this on
other systems.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
On Thu, 20 Jun 2002, Elmshauser, Erik wrote: > Hi, I am trying to select every record from a table where a specific > column does not have a value in it yet. Is there a way to express that > in a SQL statement? Here are the few I tried: > > select * from table where field; > select * from table where field = ''; > select * from table where field = NULL; > > and some other stuff the is way more broken. I have flipped through the > docs a bit but I haven't found anything useful yet. If someone could > point me to the right place in the docs or give me an example of a > select that will do this I would be very grateful. Try select * from table where field IS NULL; -- "Force has no place where there is need of skill.", "Haste in every business brings failures.", "This is the bitterest pain among men, to have much knowledge but no power." -- Herodotus
Elmshauser, Erik wrote: > Hi, I am trying to select every record from a table where a specific > column does not have a value in it yet. Is there a way to express that > in a SQL statement? Here are the few I tried: > See: http://www.postgresql.org/idocs/index.php?functions-comparison.html HTH, Joe
select * from table where field IS NULL; Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of > Elmshauser, Erik > Sent: Thursday, June 20, 2002 2:47 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] selecting all records where a column is null > > > Hi, I am trying to select every record from a table where a specific > column does not have a value in it yet. Is there a way to > express that > in a SQL statement? Here are the few I tried: > > select * from table where field; > select * from table where field = ''; > select * from table where field = NULL; > > and some other stuff the is way more broken. I have flipped > through the > docs a bit but I haven't found anything useful yet. If someone could > point me to the right place in the docs or give me an example of a > select that will do this I would be very grateful. > --Erik > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Attachment
-----Original Message-----
From: Fran Fabrizio [mailto:ffabrizio@mmrd.com]
Sent: Thursday, June 20, 2002 12:49 PM
To: Elmshauser, Erik; pgsql-general@postgresql.org
Subject: Re: [GENERAL] selecting all records where a column is null
select * from table where field is null;
is rather than =. Since null is undefined, it's not appropriate to test
for equality to null.
-Fran
Thanks to everybody who replied, that's exactly what I needed.
--Erik