Assign value to a field after checking a condition

Posted on

Question :

I have this query:

ALTER TABLE MERCHANTS
   ADD COLUMN merchant_image bytea,
   ADD COLUMN merchant_logo bytea,
   ADD COLUMN merchant_address VARCHAR(100),
   ADD COLUMN merchant_phone VARCHAR(50),
   ADD COLUMN order_type VARCHAR(15)
      CHECK(order_type IN ('TAKE_AWAY', 'HOME_DELIVERY','NO_ORDERING')),
   ADD COLUMN open_until TIME,
   ADD COLUMN order_until TIME

For order_until I have this condition: if order_type is 'NO_ORDERING' then its default value should be 0. How can I write this, any suggestions?

Answer :

order_until is data type time, which is an odd design – typically you would have timestamp or timestamptz here to include the date. Be that as it may, the column cannot be 0, which is not valid for time.

To get on with it, let’s assume you want NULL instead, which would make sense for order_type = 'NO_ORDERING'. That’s simple because the default DEFAULT value of any column is NULL and you needn’t do anything extra. Seems like you want more than just a default …

Next problem in your question: do you just want to enforce the rule or actually write NULL to the column (overwriting offending values), and if so, do that for INSERT and / or UPDATE?

To enforce your rule (exactly):

ALTER TABLE merchants ADD CONSTRAINT home_delivery_cannot_have_time
   CHECK (order_type <> 'HOME_DELIVERY' OR order_until IS NULL);

The documentation:

A check constraint can also refer to several columns.

Follow the link, there’s more.

SQL Fiddle.

Leave a Reply

Your email address will not be published. Required fields are marked *