Re: "NOT NULL" - Mailing list pgsql-novice
From | tövis |
---|---|
Subject | Re: "NOT NULL" |
Date | |
Msg-id | 004201c5603f$e3fbbfe0$3401a8c0@mainxp Whole thread Raw |
In response to | "NOT NULL" (tövis <tovises@freemail.hu>) |
List | pgsql-novice |
Thanks a lot! I'm really a newbie in SQL;o) 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. >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
pgsql-novice by date: