Re: Gradual migration from integer to bigint?

On Sun, 1 Oct 2023 at 14:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I think what you're asking for is a scheme whereby some rows in a
> table have datatype X in a particular column while other rows in
> the very same physical table have datatype Y in the same column.
> That is not happening, because there'd be no way to tell which
> case applies to any particular row.

To be honest, I don't know enough about the postgresql on-disk format
and tuple shape to be confident in how this would be solved. I was
thinking more about the ergonomics of what would be helpful and
wondering how viable it was.

Sounds like not very viable. Rats.

The docs [1] on changing column types include:

> 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

... and mention the specific case of switching between VARCHAR and
TEXT not requiring a table or index rewrite.

Seems like the specific case of int->bigint is impossible to make as
easy, given the fixed sizes in the tuple and impossibility of knowing
from tuple to tuple whether to read 4 or 8 bytes.

regards,
James

[1] https://www.postgresql.org/docs/current/sql-altertable.html