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 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?
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 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 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 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