Thread: what is null

i'm trying to alter a table call it 'my_table' and i'm trying to set the
field 'my_id' to prevent null values from being inserted. when i run the
following:

ALTER TABLE my_table ALTER my_id SET NOT NULL;

i get:

ERROR:  ALTER TABLE: Attribute "my_id" contains NULL values

so i want to find out which rows have null values so i run:

my=# select my_id from my_table where my_id=NULL;
 my_id
 ---------
 (0 rows)


so this would indicate to me that there are no rows in which my_id is NULL.
so what is preventing me from altering the table? or am i just doing
something wrong?


--
--------------------------------------------------------------------------
                                               | /"\
 john harrold                                  | \ / ASCII ribbon campaign
      jmh at member.fsf.org                    |  X  against HTML mail
           the most useful idiot               | / \
--------------------------------------------------------------------------
 What difference does it make to the dead, the orphans, and the homeless,
 whether the mad destruction is brought under the name of totalitarianism or
 the holy name of liberty and democracy?
 --Gandhi
--------------------------------------------------------------------------
gpg --keyserver keys.indymedia.org --recv-key F65A739E
--------------------------------------------------------------------------

> i'm trying to alter a table call it 'my_table' and i'm trying to set the
> field 'my_id' to prevent null values from being inserted. when i run the
> following:
>
> ALTER TABLE my_table ALTER my_id SET NOT NULL;
>
> i get:
>
> ERROR:  ALTER TABLE: Attribute "my_id" contains NULL values
>
> so i want to find out which rows have null values so i run:
>
> my=# select my_id from my_table where my_id=NULL;
>  my_id
>  ---------
>  (0 rows)
>
>
> so this would indicate to me that there are no rows in which my_id is NULL.
> so what is preventing me from altering the table? or am i just doing
> something wrong?
>
John,
Try "my_id is null" instead of "my_id=NULL"

On Mon, 4 Aug 2003, John Harrold wrote:

> i'm trying to alter a table call it 'my_table' and i'm trying to set the
> field 'my_id' to prevent null values from being inserted. when i run the
> following:
>
> ALTER TABLE my_table ALTER my_id SET NOT NULL;
>
> i get:
>
> ERROR:  ALTER TABLE: Attribute "my_id" contains NULL values
>
> so i want to find out which rows have null values so i run:
>
> my=# select my_id from my_table where my_id=NULL;
>  my_id
>  ---------
>  (0 rows)
>
>
> so this would indicate to me that there are no rows in which my_id is NULL.
> so what is preventing me from altering the table? or am i just doing
> something wrong?

Yep, you are.  But it's a common mistake for folks just learning about
NULL.

Nothing can be equal to NULL, since NULL by it's definition isn't even
equal to itself:

What you need is "is null"

select * from table where field IS NULL;

On Monday 04 August 2003 22:54, John Harrold wrote:
> i'm trying to alter a table call it 'my_table' and i'm trying to set the
> field 'my_id' to prevent null values from being inserted. when i run the
> following:
>
> ALTER TABLE my_table ALTER my_id SET NOT NULL;
>
> i get:
>
> ERROR:  ALTER TABLE: Attribute "my_id" contains NULL values
>
> so i want to find out which rows have null values so i run:
>
> my=# select my_id from my_table where my_id=NULL;
>  my_id
>  ---------
>  (0 rows)
>
>
> so this would indicate to me that there are no rows in which my_id is NULL.
> so what is preventing me from altering the table? or am i just doing
> something wrong?

select my_id from my_table where my_id IS NULL;
    or
change configuration parameter "transform_null_equals" to yes in
postgresql.conf, restart server and you can try with :
    select my_id from my_table where my_id=NULL;

Regards !