Skip site navigation (1) Skip section navigation (2)

Re: "NOT NULL"

From: "Jan B(dot)" <jan(at)monso(dot)de>
To: tövis <tovises(at)freemail(dot)hu>
Cc: pgsql novice <pgsql-novice(at)postgresql(dot)org>,Jaime Casanova <systemguards(at)gmail(dot)com>
Subject: Re: "NOT NULL"
Date: 2005-05-24 08:46:03
Message-ID: 4292E9CB.6030405@monso.de (view raw or flat)
Thread:
Lists: pgsql-novice
You MUST distinguish between NULL (that means "unknown" or "nothing") 
and ZERO (0).


To prevent a field of a table being set to NULL (unknown/nothing) you 
can add the NOT NULL contraint:

# CREATE TABLE test (some_field int NOT NULL);
CREATE TABLE

# INSERT INTO test (some_field) VALUES (NULL);
ERROR:  null value in column "some_field" violates not-null constraint

# INSERT INTO test (some_field) VALUES (0);
INSERT 141314 1


If you want to make ZERO (0) values impossible, you can use a constraint 
by using the CHECK keyword:

# CREATE TABLE test (some_field int NOT NULL, CHECK (some_field <> 0));
CREATE TABLE

# INSERT INTO test (some_field) VALUES (NULL);
ERROR:  null value in column "some_field" violates not-null constraint

# INSERT INTO test (some_field) VALUES (0);
ERROR:  new row for relation "test" violates check constraint 
"test_some_field_check"


You may want to have a look for "table constraints" at the SQL reference 
of CREATE TABLE and ALTER TABLE:
http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html


Jan Behrens


tövis wrote:
> OK
> How to prevent insert/update rows with NULL = 0 values?
> How to modify table or server change NULL = 0 values to fields DEFAULT 
> values, if is it given?
> Regards
>    Tövis
> 
> ----- Original Message ----- From: "Jaime Casanova" 
> <systemguards(at)gmail(dot)com>
> To: "tövis" <tovises(at)freemail(dot)hu>
> Cc: "pgsql novice" <pgsql-novice(at)postgresql(dot)org>
> Sent: Tuesday, May 24, 2005 7:28 AM
> Subject: Re: [NOVICE] "NOT NULL"
> 
> 
> On 5/23/05, tövis <tovises(at)freemail(dot)hu> wrote:
> 
>> Thanks Jaime!
>> Using pgAdmin III selected all rows:
>> SELECT aid,num,name30 FROM nod ORDER BY num;
>>
>> First rows from result in a CSV file:
>>
>> aid;num;name30
>> "374";"0";"2181                          "
>> "371";"0";"2178                          "
>> ...
>>
>> Is this a NULL or not? May be the method is not precise enough?
>>
>>
> The better way you can prove that is:
> SELECT aid,num,name30 FROM nod
> WHERE num IS NULL ORDER BY num;
> 
> like Andreas said NULL is not 0, NULL is "unknown".
> 
> BTW, do the reply in this thread, that will do searches in
> archives.postgresql.org easier.
> 


In response to

Responses

pgsql-novice by date

Next:From: Bendik Rognlien JohansenDate: 2005-05-24 08:48:14
Subject: Trigger unhappy
Previous:From: Andreas KretschmerDate: 2005-05-24 08:01:58
Subject: Re: [despammed] A question about postmaster and postgresql commands

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group