version
----------------------------------------------------------------
PostgreSQL 7.1 on sparc-sun-solaris2.6, compiled by GCC 2.95.2
Ok, I ported my Oracle database and my developers started complaining that
their defaults are not working. Could someone tell me if I am doing
something wrong and what the workaround is.
If I were not so new to postgres, I would think this is a bug for sure.
I tried the following cases.
--Example 1 default set null permitted.
gcf_dev=> create table test1(test varchar(1) default 'N');
CREATE
gcf_dev=> insert into test1 (test) values (null);
INSERT 38096 1
gcf_dev=> select * from test1;
test
------
(1 row)
--Example 2 default set not null
gcf_dev=> create table test2(test varchar(1) not null default 'N');
CREATE
gcf_dev=> insert into test2 (test) values (null);
ERROR: ExecAppend: Fail to add null value in not null attribute test
--Example 3 tried switching order of not null and default in definition
--Also tried inserting empty length string to see if that will get default
to fire
--No luck.
gcf_dev=> create table test3(test varchar(1) default 'N' not null);
CREATE
gcf_dev=> insert into test3 (test) values (null);
ERROR: ExecAppend: Fail to add null value in not null attribute test
gcf_dev=> insert into test3 (test) values ('');
INSERT 38119 1
gcf_dev=> select * from test3;
test
------
(1 row)