Streamhub's finance analyst is compiling SaaS costs for the annual budget. One tool's monthly price is stored as the text string '199.99' in the configuration system rather than as a number.
Write a query to return the total annual cost for a 12-month subscription.
Output:
- A single row with one column,
annual_cost.
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
'199.99'::NUMERIC * 12 AS annual_costThe shape
The ::numeric cast runs before the multiplication. Once the price is a real number the rest is straight arithmetic — the monthly cost times twelve months — and the annual cost comes back at 2399.88.
Clause by clause
'199.99'::numericis the first operand. The configuration system stored the price as a string, so the cast converts the characters1,9,9,.,9,9into the number199.99that PostgreSQL can do math with. The cast on the left of the*is what flips this from a string operation into a numeric one.* 12multiplies the cast result by the integer12. PostgreSQL sees anumericon one side and an integer on the other, promotes the integer to numeric to match, and returns the product2399.88at the same two-decimal-place scale the input carried.AS annual_costlabels the output column as the business quantity the finance analyst is compiling.
Why this and not '199.99' * 12
Without the cast, the expression is asking PostgreSQL to multiply a text value by an integer. It refuses, raising a operator does not exist: text * integer error. PostgreSQL won't quietly guess what the string was supposed to mean.
The cast is what makes the expression legal. Once '199.99' becomes 199.99, the multiplication has two compatible operands and runs the way an analyst expects. The cast has to happen before the operator; it's not a post-hoc conversion of the result.
The trap
Forget the cast and the query fails with a type error, which is the kindly version. The nastier version is the source feed quietly changing — a price arriving as a number one day and as a string the next. The defensive shape is to cast at the boundary: cast text values to numeric before the arithmetic, and the query stays robust to either source format.
You practiced casting a value before using it in arithmetic. The cast has to happen first — text values can't multiply with integers without a numeric type promotion.
