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

Re: "NOT NULL"

From: tövis <tovises(at)freemail(dot)hu>
To: "pgsql novice" <pgsql-novice(at)postgresql(dot)org>,"Jan B(dot)" <jan(at)monso(dot)de>
Subject: Re: "NOT NULL"
Date: 2005-05-24 09:06:37
Message-ID: 004201c5603f$e3fbbfe0$3401a8c0@mainxp (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks a lot!
I'm really a newbie in SQL;o)
Tövis
----- Original Message ----- 
From: "Jan B." <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>
Sent: Tuesday, May 24, 2005 10:46 AM
Subject: Re: [NOVICE] "NOT NULL"


> 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.
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


In response to

pgsql-novice by date

Next:From: tövisDate: 2005-05-24 10:37:54
Subject: Re: "NOT NULL"
Previous:From: Andreas KretschmerDate: 2005-05-24 09:03:56
Subject: Re: [despammed] Trigger unhappy

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