Thread: "NOT NULL"
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 "
"374";"0";"2181 "
"371";"0";"2178 "
...
Is this a NULL or not? May be the method is not precise enough?
am 23.05.2005, um 23:32:14 +0200 mailte tövis folgendes:
> aid;num;name30
> "374";"0";"2181 "
> "371";"0";"2178 "
> ..
>
> Is this a NULL or not? May be the method is not precise enough?
No. NULL != 0. NULL is empty, and you cant calculate with NULL.
test=# select NULL+5;
?column?
----------
(1 Zeile)
test=# select 0+5;
?column?
----------
5
(1 Zeile)
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
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. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
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.
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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. >
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 >
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.
>>
>
>
If your RAD (I don't know what this means at all) does not allow the keyword "DEFAULT" you might probably use this: INSERT INTO some_table (second_field_name, third_field_name) VALUES (second_field_value, third_field_value); If you not mention first_field_name in the first parenthesis, the default value will be used. The reference for INSERT can give you further syntax details: http://www.postgresql.org/docs/8.0/interactive/sql-insert.html Jan Behrens tövis wrote: > 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 >
Excuse me! - I always ask these kind of questions about nice, reusable and forgottable english abbreviation. Rapid Application Development - for me it is Clarion which is based on standard templates, it is support many kind of database types, has its own drivers, and of course ODBC. SQL also supported but native drivers only exists for commercial versions such as MS-SQL Prevasive, Informix... That I working through ODBC driver, and suppose to use ready to work templates for update/insert/delete and browse tables, but such a tool its covering and completelly hide the ODBC/SQL surface. It is also object oriented and I cannot give string value for an integer (especially if its length only 4 byte instead of 7 characzter such as 'DEFAULT'). This is why I use a CSTRING in database definition for Clarion and ODBC driver, and it is working well for insert and fetch records, but I could npot figure out how to give partial definition to SQL server. I need a server side solution, to change default behavior - of course if this is possible. Tövis ----- Original Message ----- From: "Jan B." <jan@monso.de> To: "tövis" <tovises@freemail.hu> Cc: "pgsql novice" <pgsql-novice@postgresql.org> Sent: Tuesday, May 24, 2005 12:54 PM Subject: Re: [NOVICE] "NOT NULL" > If your RAD (I don't know what this means at all) does not allow the > keyword "DEFAULT" you might probably use this: > > INSERT INTO some_table (second_field_name, third_field_name) VALUES > (second_field_value, third_field_value); > > If you not mention first_field_name in the first parenthesis, the default > value will be used. > > The reference for INSERT can give you further syntax details: > http://www.postgresql.org/docs/8.0/interactive/sql-insert.html > > > Jan Behrens > > > tövis wrote: > >> 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 >> > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
On Tue, May 24, 2005 at 14:40:00 +0200, tövis <tovises@freemail.hu> wrote: > covering and completelly hide the ODBC/SQL surface. It is also object > oriented and I cannot give string value for an integer (especially if its > length only 4 byte instead of 7 characzter such as 'DEFAULT'). This is why You aren't supposed to be using "'DEFAULT'", but rather "DEFAULT". It is a keyword, not a value. It still may be that your software won't let you use it. Using an explicit column list and leaving off the serial field is probably your best bet.