"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> AFAICS this is a flat-out bug too, since the per-column default should
> override the domain's default; that's certainly how it works for any
> non-null column default value. But I wasn't expecting any regression
> failures with this patch. Is it OK to change this behavior? Should I
> back-patch, or not?
Sure it's a clear-cut bug. The spec (SQL 2003 Section 11.5) clearly says the
default of the column overrides the default of the domain:
3) When a site S is set to its default value,
Case:
a) If the descriptor of S indicates that it represents a column of which some underlying column is an identity
columnor a generated column, then S is marked as unassigned.
b) If the data descriptor for the site includes a <default option>, then S is set to the value specified by
that<default option>.
c) If the data descriptor for the site includes a <domain name> that identifies a domain descriptor that
includesa <default option>, then S is set to the value specified by that <default option>.
d) If the default value is for a column C of a candidate row for insertion into or update of a derived table DT
andC has a single counterpart column CC in a leaf generally underlying table of DT, then S is set to the default
value of CC, which is obtained by applying the General Rules of this Subclause.
e) Otherwise, S is set to the null value.
I would tend to be more conservative than we've been in the past with back
patching. We keep saying people should be on the most recent point release and
people shouldn't be concerned about their application breaking. But if we make
behaviour changes, even for things which are definitely bugs, we make those
fears justified.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com