3

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)?

0

1 Answer 1

4

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.

3
  • 1
    While it may be true in this case, I don't think that "no functional difference" is always true for bracketed parts. Consider "[IF EXISTS]": It's functionally different depending on including that or not (it could error out or not). Accepting your answer, but disagreeing that bracketed parts of commands are "just noise".
    – davemyron
    Commented Jan 3, 2020 at 5:21
  • 1
    Another example would be "CREATE INDEX [ CONCURRENTLY ]" – that's definitely functionally different, yes? Regardless, thanks for your answer!
    – davemyron
    Commented Jan 3, 2020 at 5:22
  • 1
    Right. Always optional, but not always noise. I was off target there. Only noise in the case at hand, though. Commented Jan 3, 2020 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.