On Wed, Feb 14, 2024 at 11:58 PM veem v <veema0...@gmail.com> wrote:
> > > 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. > It happened when I altered columns from INTEGER to BIGINT. How do I know? The disk filled up. >
