MIME-Version: 1.0
From: Amit Langote <amitlangote09@gmail.com>
Date: Thu, 3 Apr 2014 12:31:51 +0900
Message-ID: 
 <CA+HiwqE35_+z0_gFw9mp=_0MLgPfb=+QY910LG7C-sBNTvqdgg@mail.gmail.com>
Subject: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

Hi,

When I do the following:

ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL;

The table is rewritten whereas notes section on the manual page for
ALTER TABLE says otherwise (which holds true for most of the cases
though).

http://www.postgresql.org/docs/devel/static/sql-altertable.html

As an example,

postgres=# create table test as select generate_series(1,1000000) as a;
SELECT 1000000

postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16709
(1 row)

postgres=# alter table test add column b numeric(2) DEFAULT NULL;
ALTER TABLE

-- rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column c int DEFAULT NULL;
ALTER TABLE

-- not rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column d char(5) DEFAULT NULL;
ALTER TABLE

-- rewritten, again
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16717


So, when the type of the new column has type modifier like numeric(x),
char(x) etc. do,  this happens.

Is this intentional and/or documented somewhere else? If not, should
it be documented?

--
Amit


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general