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

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

From: Kevin Brown <kevin(at)sysexperts(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #1290: Default value and ALTER...TYPE
Date: 2004-10-22 05:01:45
Message-ID: 20041022050144.GE29476@filer (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-hackers
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(# );
> > troels=# alter table lookat_feature
> > troels-#   alter column status type varchar(4);
> > 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.


Kevin Brown					      kevin(at)sysexperts(dot)com

In response to

pgsql-hackers by date

Next:From: Davide NegriDate: 2004-10-22 06:59:56
Subject: Question on the 8.0Beta Version
Previous:From: Christopher BrowneDate: 2004-10-22 03:16:49
Subject: Re: Slony-I 1.0.4 Released

pgsql-bugs by date

Next:From: Davide NegriDate: 2004-10-22 06:59:56
Subject: Question on the 8.0Beta Version
Previous:From: Jorge GodoyDate: 2004-10-22 01:02:18
Subject: Re: Instalation !

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