Re: "NOT NULL" - Mailing list pgsql-novice

Problem is evolving;o(

When I'm using a sequence for table PRIMARY KEY, where I never ever want to
give a value myself to this field I've should provide DEFAULT expression...
INSERT INTO some_table VALUES (DEFAULT,second_field,third_field,...);
But my lovely RAD (Clarion 6.1) does not allow this because of the type is a
LONG - INTEGER (4 byte).
I found a simple work around, define for RAD this value as STRING(16) - and
I can give value 'DEFAULT' - it is working.
Is there possibility to define for server that if it gets NULL or ZERO value
for this field use default value - next from sequence - which is work well
if you does not define "NOT NULL" or "PRIMARY KEY" constraint for this
field?
Thanks in advance
    Tövis

----- Original Message -----
From: "Jan B." <jan@monso.de>
To: "tövis" <tovises@freemail.hu>
Cc: "pgsql novice" <pgsql-novice@postgresql.org>; "Jaime Casanova"
<systemguards@gmail.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@gmail.com>
>> To: "tövis" <tovises@freemail.hu>
>> Cc: "pgsql novice" <pgsql-novice@postgresql.org>
>> Sent: Tuesday, May 24, 2005 7:28 AM
>> Subject: Re: [NOVICE] "NOT NULL"
>>
>>
>> On 5/23/05, tövis <tovises@freemail.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.
>>
>
>