Thread: default value not working?

                            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)

jason.servetar@ccgenesis.com writes:
> 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.

All the examples you give look perfectly fine to me.  Postgres does not
think that explicitly inserting a NULL means that the system should
substitute the default --- and the SQL standard agrees with us.
(Oracle is known to have rather broken handling of NULLs, so it wouldn't
surprise me a whole lot to hear that it gets this wrong ... but I don't
know for sure whether that's actually how it acts.)

Currently, to use a default value you have to omit the column entirely
from the column list in the INSERT command.  (SQL92 says you can also
write DEFAULT as one of the items in the VALUES list, but we don't
support that syntax yet.)

            regards, tom lane