Search Postgresql Archives

Re: adding a nullable column of type domain w/ check constraint runs checks?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk <joe@xxxxxxxxx> wrote:
I have a large table that I don't want to lock for more than couple seconds. I want to add a nullable column to the table, the type of the column is a domain with a check constraint.

It appears that the check constraint is being checked for each row, even though the column can be nullable? Is there a way around this?
BEGIN
  Timing is on.

create domain test_enum numeric check (value > 0);
  CREATE DOMAIN
  Time: 1.817 ms

create table test_enum_table (id serial primary key);
  CREATE TABLE
  Time: 2.213 ms

insert into test_enum_table select * from generate_series(1, 2000000);
  INSERT 0 2000000
  Time: 4299.000 ms

alter table test_enum_table add column t test_enum;
  ALTER TABLE
  Time: 3165.869 ms -- Takes 3 seconds in this test example
Also:

alter table test_enum_table add column t1 numeric check (t1 > 0);
  ALTER TABLE
  Time: 140.185 ms

which is much more reasonable.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux