1 Apr 2007 09:02
Re: Default value of column not respecting character length or domain restraints.
Tom Lane <tgl <at> sss.pgh.pa.us>
2007-04-01 07:02:26 GMT
2007-04-01 07:02:26 GMT
"Justin Dearing" <zippy1981 <at> gmail.com> writes:
> It seems I can feed a default value to a domain that won't fit in the
> underlying base type. I have the domain html_colors as so:
> CREATE DOMAIN html_color AS char(7) CHECK (VALUE ~ '^#[A-Fa-f0-9]{6}$');
> I then defined a column of html_colors as so:
> ALTER TABLE users ALTER COLUMN profile_color SET DEFAULT '#FFFFRFF';
> This worked fine and I didn't notice it until I added arecord to the
> users table and got the error:
> ERROR: value too long for type character(7).
> This behavior seems undesirable to me. Is this a known bug or is there
> a reason for this?
Well, the default isn't checked against constraints until it's used
at runtime. This is appropriate in a number of situations because
time-varying defaults are not uncommon (eg "default now()" for a
timestamp column). Also, defaults with side effects are not uncommon
--- think "default nextval('seq')" for a serial --- and causing those
side-effects to happen at CREATE TABLE time seems undesirable.
In short, I understand your annoyance, but the cure seems worse than
the disease. It's not like you won't find out soon enough if you
establish a constant default that doesn't meet your constraints.
regards, tom lane
---------------------------(end of broadcast)---------------------------
(Continue reading)
And as you mention - not entirely ridiculous!
Cheers
Anton
On 01/04/07, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Leonel <lnunez <at> gmail.com> writes:
> > On 4/1/07, Anton Melser <melser.anton <at> gmail.com> wrote:
> >> What will they think of next!
> >> 
RSS Feed