Thread: User-Defined Types

Greetings.

During the development of a database often there is a need to change a
field's declaration from, say, VARCHAR(32) to, say, VARCHAR(64).

Having to go through and find all pertinent uses of VARCHAR(32) in the table
definitions (where it may be a foreign key) and in functions (where it may
be a parameter) to VARCHAR(64) is tedious and error prone.

Is there a simple way to make a user-defined type that is a specific
declaration of a built-in type (like VARCHAR(##)) without having to
implement the type's support functions?

Thanks,
David

On Jul 5, 2007, at 15:53 , David F. Johnson wrote:

> During the development of a database often there is a need to change a
> field's declaration from, say, VARCHAR(32) to, say, VARCHAR(64).

> Is there a simple way to make a user-defined type that is a specific
> declaration of a built-in type (like VARCHAR(##)) without having to
> implement the type's support functions?

In PostgreSQL there's no performance advantage to limiting a varchar.
I'd recommend making all of your references to any varchar columns
not include a limit (i.e., just text or varchar), and perhaps setting
the referenced column as text with a length check constraint if one
is needed to satisfy some business rule. Then you can easily alter
the allowed length by altering the check constraint. (Similarly you
could use varchar(n) on the referenced column as well, and then just
ALTER TABLE ALTER TYPE instead of altering the check constraint. I
don't know if one has a performance advantage in checking the length
of the value.)


Michael Glaesemann
grzm seespotcode net