Question :
The docs show [SET DATA] TYPE
which implies the SET DATA
part is optional, but the 9.1 release notes also imply that SET DATA TYPE
may be different:
Allow
ALTER TABLE ... SET DATA TYPE
to avoid table rewrites in appropriate cases
I’m trying to update a column on a billion-row table from int to bigint and I’m at the four-hour mark. I’m curious if I should cancel the transaction and restart it using the SET DATA TYPE
form instead of the plain TYPE
form that I used.
Does SET DATA TYPE
do something different (and hopefully faster)?
Answer :
Keywords in square brackets in the synopsis of SQL commands in the manual are optional. And in this case just noise. Add them or leave them, no functional difference. (The verbose form SET DATA TYPE
conforms to standard SQL.)
bigint
occupies 8 bytes, integer
occupies 4 bytes. A table rewrite is inevitable. The manual:
As an exception, when changing the type of an existing column, if the
USING
clause does not change the column contents and the old type is
either binary coercible to the new type or an unconstrained domain
over the new type, a table rewrite is not needed; but any indexes on
the affected columns must still be rebuilt.
Bold emphasis mine. Those are the “appropriate cases” that don’t trigger a table rewrite since Postgres 9.1.