Thread: Default value of column not respecting character length or domain restraints.
Default value of column not respecting character length or domain restraints.
From
"Justin Dearing"
Date:
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? Regards, Justin Dearing
Re: Default value of column not respecting character length or domain restraints.
From
Tom Lane
Date:
"Justin Dearing" <zippy1981@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