This translates exactly to your suggestion, but based on the fact that django abstracts it I would not expect to need to cast anything to decimal since my columns are already decimal (which I know understand maps to NUMERIC and not REAL). For example, I can make that multiplication that you described like this: T.objects.annotate(įoo=(Value(Decimal("1.0")) * F("a")) / F("b"), Perhaps an adjusted query is all you need, if other fixes are off the table / slow.īut I can actually so some "advanced stuff" like that. SELECT ((a * 1.0) / b) FROM t - Using another calculation to force float SELECT (CAST(a AS REAL) / b) FROM t - Casting one or both values SELECT CAST(a / b AS REAL) FROM t - Not useful SELECT a / b FROM t - Integer division only However, on the off chance you were just interested in a solution, consider the following sql where both table t's columns are INTeger (not even Decimal or Numeric) and see what works and do not work when forcing the calculation types: Hi Thiago, there's been good and accurate replies to this post, showing what is correct and why. The only way I got the real result from this was when I casted price to real: sqlite> SELECT price, original_price, cast(price as real) / coalesce(original_price, price) FROM product_product ĪFAIK, even the result without cast should be working and giving the right result, right?ītw, I'm using sqlite3 from the current debian unstable build: ❯ sqlite3 -versionģ.34.1 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1 I did a test by forcing price to be casted to numeric (I tried decimal too): sqlite> SELECT price, original_price, cast(price as numeric) / coalesce(original_price, price) FROM product_product And as you can see on my schema, they are indeed decimals and not integers. Here is what I get: sqlite> SELECT price, original_price, price / coalesce(original_price, price) FROM product_product I want to select the price, original price and price / original_price (both columns of decimal type). schema product_productĬREATE TABLE IF NOT EXISTS "product_product" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "name" varchar(255) NOT NULL, "price" decimal NOT NULL, "quantity" decimal NOT NULL, "depth" decimal NULL, "height" decimal NULL, "weight" decimal NULL, "width" decimal NULL, "freight_help" decimal NOT NULL, "ref" char(32) NOT NULL UNIQUE, "erp_id" varchar(255) NULL UNIQUE, "erp_synced_at" datetime NULL, "unit" varchar(2) NOT NULL, "is_active" bool NOT NULL, "kind" varchar(9) NOT NULL, "original_price" decimal NULL, "coffee_kind" varchar(7) NULL, "producer_id" integer NULL REFERENCES "product_producer" ("id") DEFERRABLE INITIALLY DEFERRED, "region_id" integer NULL REFERENCES "product_region" ("id") DEFERRABLE INITIALLY DEFERRED, "variety_id" integer NULL REFERENCES "product_variety" ("id") DEFERRABLE INITIALLY DEFERRED, "altitude" integer NULL, "characteristics" varchar(255) NULL, "score" integer NULL, "olfactory" varchar(255) NULL, "taste" varchar(255) NULL, "visual" varchar(255) NULL, "is_fresh" bool NOT NULL, "is_featured" bool NOT NULL, "description" text NOT NULL, "sca_report" varchar(2000) NULL, "stock" integer NULL, "slug" varchar(50) NOT NULL) ĬREATE INDEX "product_product_is_active_79c22e04" ON "product_product" ("is_active") ĬREATE INDEX "product_product_producer_id_48defcb2" ON "product_product" ("producer_id") ĬREATE INDEX "product_product_region_id_8af442d6" ON "product_product" ("region_id") ĬREATE INDEX "product_product_variety_id_899ca5d0" ON "product_product" ("variety_id") ĬREATE INDEX "product_product_is_fresh_8e90cb7e" ON "product_product" ("is_fresh") ĬREATE INDEX "product_product_is_featured_27b90897" ON "product_product" ("is_featured") ĬREATE INDEX "product_product_slug_76cde0ae" ON "product_product" ("slug") Here is the schema for my table: sqlite>. I just had this issue where a division by 2 decimals is rounding some numbers if they don't have a floating point in the actual value.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |