The following bug has been logged online:

Bug reference: 1290
Logged by: Troels Arvin

Email address: troels (AT) arvin (DOT) dk

PostgreSQL version: 8.0 Beta

Operating system: Linux, Fedora Core 2 + stuff from Red Hat Rawhide

Description: Default value and ALTER...TYPE

Details:

In latest CVS (updated 2004-10-20 18:30 CEST), a too-large default column
value seems to block the complete effects of an ALTER TABLE ... ALTER COLUMN
.... TYPE operation, see below:

troels=# select version();
version
---------------------------------------------------------------------------
-----------------------------
PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2
20040907 (Red Hat 3.4.2-2)
(1 row)

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=# \d lookat_feature
Table "public.lookat_feature"
Column | Type | Modifiers
------------+----------------------+-----------------------------------
feature_id | character(4) |
status | character varying(4) | default 'TODO'::character varying

troels=# insert into lookat_feature (feature_id) values('B034');
ERROR: value too long for type character varying(2)


If instead, the "DEFAULT 'TODO'" is left out for the "status" column:

troels=# create table lookat_feature(
troels(# feature_id char(4),
troels(# status varchar(2)
troels(# );
CREATE TABLE
troels=# alter table lookat_feature
troels-# alter column status type varchar(4);
ALTER TABLE
troels=# \d lookat_feature
Table "public.lookat_feature"
Column | Type | Modifiers
------------+----------------------+-----------
feature_id | character(4) |
status | character varying(4) |

troels=# insert into lookat_feature (feature_id) values('B034');
INSERT 17073 1



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
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?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, 20 Oct 2004 14:07:29 -0400, Tom Lane wrote:

Would it be possible to check the compatibility of a default value for
the associated column? Such a check might probably have to be limited to
checking of scalar default values - not function calls - to make it
doable.
This would be nice anyway, to catch stupid CREATE TABLE errors earlier.

If such a compatibility check could somehow be implemented, then the check
could be applied for

- CREATE TABLE ...
- ALTER TABLE ... ALTER COLUMN ... SET DEFAULT...
- ALTER TABLE ... ALTER COLUMN ... TYPE...
- CREATE DOMAIN ...

--
Greetings from Troels Arvin, Copenhagen, Denmark



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Troels Arvin <troels (AT) arvin (DOT) dk> writes:
I think that would introduce as many problems as it would fix. AFAICS
the only way to make such a check is to evaluate the expression and see
what happens. There are significant cases in which this is a bad idea
--- consider "default nextval('seq')". I think people would be
justifiably upset if we changed the state of their sequences just
through installing such a default.

Another issue: consider a column definition like
foo integer not null
The default (NULL) is explicitly not a legal value for the column.
But this is not wrong, nor even bad practice. The intent may be to
force users to explicitly supply a value in all cases. Suppose that
you want to force that, but you want it to be okay to explicitly supply
NULL --- then you can't use "not null", so AFAICS the only way is to
specify a default value that will fail if used. This works:

regression=# create table t1(f1 int default 'please supply a value'::text::int);
CREATE TABLE
regression=# insert into t1 default values;
ERROR: invalid input syntax for integer: "please supply a value"
regression=#

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org