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

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: adding a nullable column of type domain w/ check constraint runs checks?
Date: 2014-08-19 22:10:53
Message-ID: CACfv+pJ2HATOBUoG2MkeA84wSpK5MWVxF9YvvREtBBm2JZeyUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Van Dyk 2014-08-19 22:16:37 Re: adding a nullable column of type domain w/ check constraint runs checks?
Previous Message Adrian Klaver 2014-08-19 21:53:53 Re: ERROR: Problem running post install step