February 14th, 2012 by depesz | Tags: alter, alter table, alter type, length, pg92, postgresql, precision, reindex, rewrite | 1 comment »

Did it help? If yes - maybe you can help me?

Did it help? If yes - maybe you can help me?

Three patches for you today, all committed by Robert Hass:

- On 7th of February, patch:
Add a transform function for numeric typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds when a column is changed to an unconstrained numeric, or when the scale is unchanged and the precision does not decrease. Noah Misch, with a few stylistic changes and a fix for an OID collision by me.

- also on 7th, patch:
Add a transform function for varbit typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds when the new type is unconstraint varbit, or when the allowable number of bits is not decreasing. Noah Misch, with review and a fix for an OID collision by me.

- and a day later final patch:
Add transform functions for various temporal typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds in some cases. Noah Misch, with trivial changes by me.

Those patches are expanding what was previously committed in these two changes.

Since I already covered what it does, and what good it is, I'll just enumerate new cases where rewrites are skipped:

- numeric(x,y) to numeric(x+,y) – for example from numeric(8,3) to numeric(10,3)
- any numeric to numeric without specification of precision/scale, like:
alter table z alter column i type numeric;

- varbit(x) to varbit(x+) – for example from varbit(8) to varbit(10)
- any varbit column to varbit without length limit, like:
alter table z alter column i type varbit;

- time(x) to time(x+) – for example from time(2) to time(3)
- any time column to time without precision limit, like:
alter table z alter column i type time;

- timetz(x) to timetz(x+) – for example from timetz(2) to timetz(3)
- any timetz column to timetz without precision limit, like:
alter table z alter column i type timetz;

- timestamp(x) to timestamp(x+) – for example from timestamp(2) to timestamp(3)
- any timestamp column to timestamp without precision limit, like:
alter table z alter column i type timestamp;

- interval(x) to interval(x+) – for example from interval(2) to interval(3)
- any interval column to interval without precision limit, like:
alter table z alter column i type interval;

- from: “INTERVAL YEAR" to:
- “INTERVAL YEAR"
- “INTERVAL DAY"
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL YEAR TO MONTH"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL MONTH" to:
- “INTERVAL YEAR"
- “INTERVAL MONTH"
- “INTERVAL DAY"
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL YEAR TO MONTH"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL DAY" to:
- “INTERVAL DAY"
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL HOUR" to:
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL MINUTE" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL SECOND" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL YEAR TO MONTH" to:
- “INTERVAL YEAR"
- “INTERVAL DAY"
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL YEAR TO MONTH"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL DAY TO HOUR" to:
- “INTERVAL HOUR"
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO HOUR"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL DAY TO MINUTE" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL DAY TO SECOND" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL HOUR TO MINUTE" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL HOUR TO SECOND" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

- from: “INTERVAL MINUTE TO SECOND" to:
- “INTERVAL MINUTE"
- “INTERVAL SECOND"
- “INTERVAL DAY TO MINUTE"
- “INTERVAL DAY TO SECOND"
- “INTERVAL HOUR TO MINUTE"
- “INTERVAL HOUR TO SECOND"
- “INTERVAL MINUTE TO SECOND"

Most of these datatypes are pretty exotic, but they do have their uses, and the ability to convert without table/index rewrite is definitely a good thing to have. Thanks Noah and Robert.

## One comment

This is very helpful, thank you.

What about from timestamp to timestamptz?