Re: lack of consequence with domains and types

On Dec 24, 2008, at 12:04 PM, Grzegorz Jaśkiewicz wrote:

> On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones <ejones(at)engineyard(dot)com>
> wrote:
>> Yes, and columns have default values, too, which are not tied to
>> their
>> datatype's default value (if it even has one). ALTER TABLE
>> initializes rows
>> to have the new *column's* default. A column of some domain type
>> could
>> easily have some default other than the domain's default and, in
>> fact, if
>> you don't specify a default for the column then it's default is NULL.
>
> the whole thing about domains, is that you specify type and default,
> and even check constraint. And I did specify default - hence I would
> expect it to be set to that value!!

You really need to understand the difference between a domain's
default and a column's default. The ALTER TABLE docs specifically say
that if you don't specify a default for the new *column* then that
column is set to NULL for all rows. That is not the same as not
providing a value for a column of some domain type with a default in
an INSERT statement. A domain with a default does not specify that it
can not be set to null:

pagila=# create domain foo_domain as integer default 5;
CREATE DOMAIN
Time: 289.269 ms
pagila=# create table foo_table (a int, b foo_domain);
CREATE TABLE
Time: 22.784 ms
pagila=# insert into foo_table (a, b) values (1, null);
INSERT 0 1
Time: 1.489 ms
pagila=# insert into foo_table (a) values (1);
INSERT 0 1
Time: 1.582 ms
pagila=# select * from foo_table;
a | b
---+----
1 | \N
1 | 5
(2 rows)

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k