Waxren Waxren - 5 months ago 10
MySQL Question

Conditional Index in PostgreSQL Database

I am working with PostgreSQL Database version 9.5 on Windows 10 Pro x64.

In my database I have two integer columns one for all Quantity and one for the available quantity.
What I basically want is to prevent insert or update if All Quantity is less than Available Quantity so I have created this index:

CREATE TABLE IF NOT EXISTS "Items"("Id" SERIAL PRIMARY KEY, "Name" VARCHAR(255) NOT NULL UNIQUE,
"Barcode" VARCHAR(255) NOT NULL UNIQUE, "AllQuantity" INTEGER NOT NULL, "AvailableQuantity" INTEGER NOT NULL,
"PurchasingPrice" NUMERIC NOT NULL, "SellingPrice" NUMERIC NOT NULL, "ProductionDate" DATE DEFAULT NULL, "ExpirationDate" DATE DEFAULT NULL,
"Description" TEXT DEFAULT NULL, "OtherDetails" TEXT DEFAULT NULL);

CREATE INDEX items_quantity ON "Items" (("AllQuantity" > "AvailableQuantity"));


The problem is that the index doesn't work and I can insert to the table with a row containing all quantity value that is less than available quantity. I just tried this query and it returned successfully.

INSERT INTO "Items" VALUES(1,'some_item','1234567891234',15,20,20,40,'2016-08-08','2016-09-22');

Answer

If you want to apply some business logic on your table you need to use a constraint, not an index:

ALTER TABLE "Items" 
ADD CONSTRAINT "Items_Avialability_Check" 
CHECK ("AllQuantity" > "AvailableQuantity");

With this constraint in place, if you attempt the same insert you stated in the OP, you'll get the following error:

ERROR: new row for relation "Items" violates check constraint "Items_Avialability_Check" DETAIL: Failing row contains (1, some_item, 1234567891234, 15, 20, 20, 40, 2016-08-08, 2016-09-22, null, null).