AW: O$: Null Fields

Rudolf Bargholz bargholz at
Wed Apr 22 10:08:53 EDT 2009

Hi Cliff,

Just spent a couple of days thinking I was very smart and attempting to implement what you describe below. I went through all our schemas and assigned an additional property to the schema column in order to set a DEFAULT value, only to find out in the end if you explicitly insert a NULL into a column (at least with DB2), then DB2 will insert the NULL, irrespective of the value you supply as a default. Only if you do not supply the column in your insert statement will the default value be inserted.

In your example below, could you try:

insert into test (id, foo, bar) values (default, cast(NULL as char(1)), 99);

and have a look what you get returned from the select? In DB2, to my dismay, it was NULL and not the expected empty value. Could have kicked myself following this white elephant and wasting so much time without testing thoroughly first. A

Do Row.$definefromsqlclass('MySchema')
Do Row.$insert()

till thus not insert any DEFAULT values, but rather NULL in all of the specified columns.


Rudolf Bargholz

-----Ursprüngliche Nachricht-----
Von: omnisdev-en-bounces at [mailto:omnisdev-en-bounces at] Im Auftrag von CLIFFORD ILKAY
Gesendet: Dienstag, 21. April 2009 19:53
An: OmnisDev List - English
Betreff: Re: O$: Null Fields

Hash: SHA1

Christine Penner wrote:
> We use Postgres.

Use the Force, Christine. :) If you want to avoid NULLs, why not set
default values for those columns in which you don't want NULLs? The
following illustrates.

christine=# create table test (id serial primary key, foo varchar not
null default 'BLUB', bar integer default 0);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for
serial column ""
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
Time: 36.697 ms
christine=# insert into test default values;
INSERT 12523162 1
Time: 13.921 ms
christine=# insert into test (id, foo, bar) values (default, 'Hello',
INSERT 12523163 1
Time: 9.422 ms
christine=# insert into test (id, foo, bar) values (default, default, 99);
INSERT 12523164 1
Time: 5.471 ms
christine=# select * from test;
 id |  foo  | bar
- ----+-------+-----
  1 | BLUB  |   0
  2 | Hello |   0
  3 | BLUB  |  99
(3 rows)

Time: 0.507 ms

See <>.
- --

Clifford Ilkay
1419-3266 Yonge St.
Toronto, ON
Canada  M4N 3P6

+1 416-410-3326
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla -

Manage your list subscriptions at

More information about the omnisdev-en mailing list