On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk <joe@tanga.com> 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
