Re: [BUGS] BUG #1290: Default value and ALTER...TYPE

Tom Lane wrote:
> "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> > troels=# create table lookat_feature(
> > troels(# feature_id char(4),
> > troels(# status varchar(2) default 'TODO'
> > troels(# );
> > CREATE TABLE
> > troels=# alter table lookat_feature
> > troels-# alter column status type varchar(4);
> > ALTER TABLE
> > troels=# insert into lookat_feature (feature_id) values('B034');
> > ERROR: value too long for type character varying(2)
>
> Hmm. What's going on here is that the stored default expression is
> actually of the form
> ('TODO'::varchar)::varchar(2)
> where you don't see the coercion to varchar(2) in \d becayuse ruleutils.c
> doesn't show implicit casts. After the ALTER COLUMN it's of the form
> (('TODO'::varchar)::varchar(2))::varchar(4)
> which of course will give an error when used.
>
> Possibly we should make ALTER COLUMN strip any implicit coercions that
> appear at the top level of the default expression before it adds on the
> implicit coercion to the new column datatype. I am not sure that this
> is a good idea, however; it seems like it might alter the semantics in
> unexpected ways. (The default expression could potentially come through
> differently than an actually stored value of the column would do.)
>
> The alternative would seem to be decreeing that this is not a bug.
>
> Comments anyone?

I think the conversion from 'TODO' to varchar(2) and then to
varchar(4) is much more surprising than converting 'TODO' directly to
varchar(4) after the ALTER TABLE. In short, as a DBA I would expect
the database to do any conversion into the column target type based on
the original specified default value, and not some intermediate form
that exists only because of the history of the column's datatype.

So, my vote is for the form to be ('TODO'::varchar)::varchar(4) after
the ALTER TABLE in the example.

FWIW...

--
Kevin Brown kevin(at)sysexperts(dot)com