AW: O$: Null Fields
CLIFFORD ILKAY
clifford_ilkay at dinamis.com
Wed Apr 22 19:28:24 EDT 2009
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 22/04/09 10:08 AM, Rudolf Bargholz wrote:
> 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
Hi Rudolf,
PostgreSQL seems to know the difference between '' and NULL. See below.
cilkay at prometheus:~$ createdb christine
cilkay at prometheus:~$ psql christine
Timing is on.
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
christine=# create table test (id serial primary key, foo varchar not
null default '', bar integer default 0);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
Time: 32.516 ms
christine=# insert into test default values;
INSERT 0 1
Time: 16.741 ms
christine=# insert into test (id, foo, bar) values (default, 'Hello',
default);
INSERT 0 1
Time: 20.094 ms
christine=# insert into test (id, foo, bar) values (default, default, 99);
INSERT 0 1
Time: 20.063 ms
christine=# insert into test (id, foo, bar) values (default, cast(NULL
as char(1)), 111);
ERROR: null value in column "foo" violates not-null constraint
christine=# select * from test;
id | foo | bar
- ----+-------+-----
1 | | 0
2 | Hello | 0
3 | | 99
(3 rows)
Time: 0.575 ms
christine=# select * from test where foo = '';
id | foo | bar
- ----+-----+-----
1 | | 0
3 | | 99
(2 rows)
Time: 1.801 ms
christine=# select * from test where foo is null;
id | foo | bar
- ----+-----+-----
(0 rows)
Time: 0.341 ms
christine=#
- --
Regards,
Clifford Ilkay
Dinamis
1419-3266 Yonge St.
Toronto, ON
Canada M4N 3P6
<http://dinamis.com>
+1 416-410-3326
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFJ76gXlUaSlL+/juwRAi+lAKCp+A8qUkm2EP66QoO9xACYM/lQegCgvgkw
U7F+HY1H/nIFlpumcmoYi1Q=
=teKH
-----END PGP SIGNATURE-----
More information about the omnisdev-en
mailing list