[NOVICE] "NOT NULL"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[NOVICE] "NOT NULL"

tövis
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?
 
Reply | Threaded
Open this post in threaded view
|

Re: [despammed] [NOVICE] "NOT NULL"

Andreas Kretschmer
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    ===

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

Jaime Casanova
In reply to this post by tövis
On 5/23/05, tövis <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

tövis
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" <[hidden email]>
To: "tövis" <[hidden email]>
Cc: "pgsql novice" <[hidden email]>
Sent: Tuesday, May 24, 2005 7:28 AM
Subject: Re: [NOVICE] "NOT NULL"


On 5/23/05, tövis <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

Jan B.
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"
> <[hidden email]>
> To: "tövis" <[hidden email]>
> Cc: "pgsql novice" <[hidden email]>
> Sent: Tuesday, May 24, 2005 7:28 AM
> Subject: Re: [NOVICE] "NOT NULL"
>
>
> On 5/23/05, tövis <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

tövis
Thanks a lot!
I'm really a newbie in SQL;o)
Tövis
----- Original Message -----
From: "Jan B." <[hidden email]>
To: "tövis" <[hidden email]>
Cc: "pgsql novice" <[hidden email]>; "Jaime Casanova"
<[hidden email]>
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"
>> <[hidden email]>
>> To: "tövis" <[hidden email]>
>> Cc: "pgsql novice" <[hidden email]>
>> Sent: Tuesday, May 24, 2005 7:28 AM
>> Subject: Re: [NOVICE] "NOT NULL"
>>
>>
>> On 5/23/05, tövis <[hidden email]> 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
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

tövis
In reply to this post by Jan B.
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." <[hidden email]>
To: "tövis" <[hidden email]>
Cc: "pgsql novice" <[hidden email]>; "Jaime Casanova"
<[hidden email]>
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"
>> <[hidden email]>
>> To: "tövis" <[hidden email]>
>> Cc: "pgsql novice" <[hidden email]>
>> Sent: Tuesday, May 24, 2005 7:28 AM
>> Subject: Re: [NOVICE] "NOT NULL"
>>
>>
>> On 5/23/05, tövis <[hidden email]> 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 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

Jan B.
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
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

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." <[hidden email]>
To: "tövis" <[hidden email]>
Cc: "pgsql novice" <[hidden email]>
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
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] "NOT NULL"

Bruno Wolff III
On Tue, May 24, 2005 at 14:40:00 +0200,
  tövis <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings