Re: ALTER TABLE modifications

On Wed, 2003-11-12 at 14:02, Peter Eisentraut wrote:
> Rod Taylor writes:
>
> > ALTER TABLE tab ADD COLUMN col DEFAULT 3, ADD CHECK (anothercol > 3);

> I think it's perfectly fine to write two separate ALTER TABLE statements.
> No need to introduce this nonstandard syntax.

Yes, it is certainly fine to do so, but much faster to do the above.

The command shown executes nearly 40% faster than 2 independent
statements in a single transaction -- the difference is even more
significant with additional sub-commands.

> > ALTER TABLE tab ALTER COLUMN col TYPE text TRANSFORM ...;
> > Currently migrates indexes, check constraints, defaults, and the
> > column definition to the new type with optional transform. If
> > the tranform is not supplied, a standard assignment cast is
> > attempted.
>
> Please don't use the term "transform". It is used by the SQL standard for
> other purposes. What kind of object would you put in place of the "..."
> anyway? A function? What syntax do other databases use?

I've not found another database which allows this syntax. The suggestion
of TRANSFORM was Toms and was a result of using an assignment cast by
default. Do you have a better term I can use?

http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=266b02a270a164aa&seekm=1064805960.60248.24.camel%40jester#link4

The ... is an A_Expr which does not accept (among other things)
subselects. CASE statements, equations, etc. work fine.

CREATE TABLE tab (col int2);
-- integer to boolean
ALTER TABLE tab ALTER col TYPE boolean
TRANSFORM CASE WHEN col >= 1 THEN true ELSE false END;

-- or say Bytes to MBytes (original column is int8)
ALTER TABLE tab ALTER col TYPE integer TRANSFORM col / (1024 * 1024);