Обсуждение: [GENERAL] Best way to allow column to initially be null?
Hi, I’m trying to make a column have these properties: 1. When a row is inserted, this column is allowed to be null. 2. When the row is updated, no null can be assigned to it this column. I initially thought I can drop the not null constraint before insertion and turn it back on after that, but after readingthe doc it seems turning on not null constraint requires not columns contain null value, so looks like it won’t work. My current approach is to not set the not null constraint in the table and use a before update trigger to manually raiseexception when the column is null. But it doesn’t seem as elegant. Is there a better way? Regards, Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Glen Huang wrote:> I’m trying to make a column have these properties:>> 1. When a row is inserted, this column is allowedto be null. 2. When the row is updated, no null> can be assigned to it this column.>> I initially thought I can dropthe not null constraint before insertion and turn it back on after> that, but after reading the doc it seems turningon not null constraint requires not columns> contain null value, so looks like it won’t work.>> My current approachis to not set the not null constraint in the table and use a before update> trigger to manually raise exception whenthe column is null. But it doesn’t seem as elegant.>> Is there a better way?> Sounds to me like a BEFORE UPDATE trigger is exactly the way to handle this. Rejecting invalid data input values is an ideal use case for such a facility. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Good to know I’m not doing something stupid. Thanks. > On 30 Sep 2017, at 8:51 PM, Berend Tober <btober@computer.org> wrote: > > Glen Huang wrote: > > I’m trying to make a column have these properties: > > > > 1. When a row is inserted, this column is allowed to be null. 2. When the row is updated, no null > > can be assigned to it this column. > > > > I initially thought I can drop the not null constraint before insertion and turn it back on after > > that, but after reading the doc it seems turning on not null constraint requires not columns > > contain null value, so looks like it won’t work. > > > > My current approach is to not set the not null constraint in the table and use a before update > > trigger to manually raise exception when the column is null. But it doesn’t seem as elegant. > > > > Is there a better way? > > > > Sounds to me like a BEFORE UPDATE trigger is exactly the way to handle this. Rejecting invalid data input values is anideal use case for such a facility. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general