On Thu, 15 Feb 2024 at 00:43, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> It depends: > > https://www.postgresql.org/docs/current/sql-altertable.html > > "Adding a column with a volatile DEFAULT or changing the type of an > existing column will require the entire table and its indexes to be > rewritten. 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. However, indexes must always > be rebuilt unless the system can verify that the new index would be > logically equivalent to the existing one. For example, if the collation for > a column has been changed, an index rebuild is always required because the > new sort order might be different. However, in the absence of a collation > change, a column can be changed from text to varchar (or vice versa) > without rebuilding the indexes because these data types sort identically. > Table and/or index rebuilds may take a significant amount of time for a > large table; and will temporarily require as much as double the disk space. > > " > > > create table int_test(int_fld integer); > > insert into int_test select * from generate_series(1, 10000, 1); > > > select ctid, int_fld from int_test ; > > ctid | int_fld > ----------+--------- > (0,1) | 1 > (0,2) | 2 > (0,3) | 3 > (0,4) | 4 > (0,5) | 5 > (0,6) | 6 > (0,7) | 7 > (0,8) | 8 > (0,9) | 9 > (0,10) | 10 > > > alter table int_test alter column int_fld set data type bigint; > > select ctid, int_fld from int_test ; > > ctid | int_fld > ----------+--------- > (0,1) | 1 > (0,2) | 2 > (0,3) | 3 > (0,4) | 4 > (0,5) | 5 > (0,6) | 6 > (0,7) | 7 > (0,8) | 8 > (0,9) | 9 > (0,10) | 10 > > update int_test set int_fld = int_fld; > > select ctid, int_fld from int_test order by int_fld; > > (63,1) | 1 > (63,2) | 2 > (63,3) | 3 > (63,4) | 4 > (63,5) | 5 > (63,6) | 6 > (63,7) | 7 > (63,8) | 8 > (63,9) | 9 > (63,10) | 10 > > > Where ctid is: > > https://www.postgresql.org/docs/current/ddl-system-columns.html > > > Thank you so much. So as I also tested the same as you posted, there has been no change in "ctid" , when I altered the column data type from 'int' to 'bigint' in the table, so that means full table rewriting won't happen in such a scenario. Regards Veem
