Postgres Pro
Downloads
Home   >   mailing lists

Thread: Retrieving NULL records

Retrieving NULL records

From
"psql novice"
Date:
Hi,

How would you retrieve records containing NULL values?

Lets say you have a table called stock:

stock=> \d stock
                                      Table "public.stock"
  Column  |         Type          |                          Modifiers
----------+-----------------------+-------------------------------------------------------------
 stock_id | integer               | not null default nextval('public.stock_stock_id_seq'::text)
 name     | character varying(20) | not null
 qty      | integer               |

it contains the following records:

stock=> select * from stock;
 stock_id |   name    | qty
----------+-----------+-----
        1 | Chair     |  10
        2 | Desk      |  10
        3 | Phone     |  10
        4 | Chalk     |
        5 | Projector |
(5 rows)

So what i want to do is retrieve 'Chalk' and 'Projector';

Ive tried this but it didnt work.

stock=> select * from stock where qty = NULL;
 stock_id | name | qty
----------+------+-----
(0 rows)


Any help would be appreciated.

Thanks in advance
--
____________________________________________
http://www.operamail.com
Get OperaMail Premium today - USD 29.99/year


Powered by Outblaze

Re: Retrieving NULL records

From
Tom Lane
Date:
"psql novice" <psql_novice@operamail.com> writes:
> stock=> select * from stock where qty = NULL;

The correct incantation is

 select * from stock where qty IS NULL;

Ordinary comparisons involving NULL always fail (or more accurately,
return NULL).  You have to use the special "is null" operator instead.

            regards, tom lane

Re: Retrieving NULL records

From
Date:
Shouldn't  = NULL  be considered a syntax error ?


Tom Lane <tgl@sss.pgh.pa.us> schrieb am 29.07.2003, 07:41:15:
> "psql novice"  writes:
> > stock=> select * from stock where qty = NULL;
>
> The correct incantation is
>
>  select * from stock where qty IS NULL;
>
> Ordinary comparisons involving NULL always fail (or more accurately,
> return NULL).  You have to use the special "is null" operator instead.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

Re: Retrieving NULL records

From
"Mel Jamero"
Date:
it is now..

i had to port lots of our old C applications when i upgraded to a newer
postgres.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of
email@juergen-cappel.de
Sent: Tuesday, July 29, 2003 2:40 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Retrieving NULL records



Shouldn't  = NULL  be considered a syntax error ?


Tom Lane <tgl@sss.pgh.pa.us> schrieb am 29.07.2003, 07:41:15:
> "psql novice"  writes:
> > stock=> select * from stock where qty = NULL;
>
> The correct incantation is
>
>  select * from stock where qty IS NULL;
>
> Ordinary comparisons involving NULL always fail (or more accurately,
> return NULL).  You have to use the special "is null" operator instead.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Retrieving NULL records

From
Nabil Sayegh
Date:
Am Die, 2003-07-29 um 13.20 schrieb Mel Jamero:
> it is now..
>
> i had to port lots of our old C applications when i upgraded to a newer
> postgres.

I know that it's bad to use null=null, but for old projects that 'just
work' the way they were programmed you can set

transform_null_equals = true

in postgresql.conf

hth
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de