From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL |
Date: | 2014-04-03 03:31:51 |
Message-ID: | CA+HiwqE35_+z0_gFw9mp=_0MLgPfb=+QY910LG7C-sBNTvqdgg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-03 03:54:32 | Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL |
Previous Message | Sehrope Sarkuni | 2014-03-28 19:51:47 | Patch to add results for JSON operator examples |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-03 03:54:32 | Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL |
Previous Message | Adrian Klaver | 2014-04-03 02:40:31 | Re: COPY v. java performance comparison |