Skip site navigation (1) Skip section navigation (2)

Re: lack of consequence with domains and types

From: Erik Jones <ejones(at)engineyard(dot)com>
To: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: lack of consequence with domains and types
Date: 2008-12-24 23:41:20
Message-ID: D504DDC8-4839-4F7E-AE1B-B442FDC52C74@engineyard.com (view raw or flat)
Thread:
Lists: pgsql-general
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






In response to

Responses

pgsql-general by date

Next:From: Asko OjaDate: 2008-12-25 01:36:47
Subject: Re: pl/proxy and sequence generation
Previous:From: Grzegorz JaśkiewiczDate: 2008-12-24 20:04:34
Subject: Re: lack of consequence with domains and types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group