Re: "NOT NULL"

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
>