You've spent three lessons writing ::TEXT, ::NUMERIC and ::DATE without anybody explaining what they are. And you've been carrying a promise for two modules: 04-03 closed the subject of nulls by saying that COALESCE and NULLIF —the tools that replace a null with something presentable— would be explained here. The two topics go together because they're the same thing seen from two angles: what to do when a value doesn't have the shape you need. Sometimes it has the wrong type and has to be converted; sometimes there's no value at all and it has to be substituted. They're the glue holding everything before them together: without them, string functions break on a null, numeric ones fail dividing by zero and date ones refuse to read a piece of text.

1. Implicit against explicit conversion
2. CAST and the :: operator
3. The usual conversions and their traps
4. to_number, to_char, to_date: controlled conversion
5. COALESCE: the first non-null value
6. NULLIF and its two canonical uses
7. COALESCE against CASE
8. COALESCE in aggregates: 04-04's debt
9. Comparison table by engine
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

An implicit conversion is one the engine performs by itself, without being asked. An explicit one is one you write.

All three columns use the same '9' or '2' and they behave in three different ways. 1 + '2' gives 3 because PostgreSQL sees an integer on the left and resolves the literal as an integer; 10 > '9' gives true by the same mechanism; and '10' > '9' gives false, because there's no type there to give the hint, both literals get resolved as text and in alphabetical order '10' comes before '9'.

That last one is the example of why implicit conversion is dangerous: it doesn't fail, it returns something else. A WHERE code > '9' over a text column containing numbers filters the opposite way from what you think, and nobody warns you.

The rule: when two types get mixed, convert them yourself. The engine knowing how to guess doesn't mean it guesses what you want, and it certainly doesn't mean the next engine will guess the same way.

There are two syntaxes for the same thing:

CAST(expr AS type) is standard SQL and works on every engine; expr::type is PostgreSQL's shorthand, shorter and handy when chaining but not portable. The practical advice: :: in analysis queries, CAST in code that might end up on another engine. And watch the precedence: :: is applied before the arithmetic operators, so a + b::NUMERIC converts only b; to convert the sum, (a + b)::NUMERIC.

Notice the third column already: CAST(12.9 AS INTEGER) returns 13, not 12. That's the first trap.

And this is good news: the error stops the query and forces you to look at the data. Compare it with SQLite, which for the same expression returns 0 without saying anything, so a SUM over that column would give a plausible and wrong total. PostgreSQL being strict is a feature, not a nuisance.

The real case shows up when importing a text column with '12,50' in continental European format. '12,50'::NUMERIC fails, and the solution is to normalise beforehand with 06-01's functions:

12.4::INTEGER is 12, but 12.5::INTEGER and 12.9::INTEGER are both 13. It's consistent with 06-02 —NUMERIC rounds half-up— and it contradicts the intuition of anybody coming from a programming language, where converting to an integer truncates. If you want to truncate, write TRUNC(12.9)::INTEGER, which does give 12.

'03/04/2025'::DATE has no single answer: it depends on the session parameter DateStyle, which defaults to ISO, MDY (ISO output, month-day-year input) and is inspected with SHOW DateStyle;. With that value, '03/04/2025'::DATE is 4 March; with SET DateStyle = 'ISO, DMY'; it becomes 3 April. The same string, two dates, depending on a session variable almost nobody looks at.

The rule, already seen in 06-03: for ambiguous dates always use TO_DATE(text, pattern) with an explicit pattern, or demand the ISO format YYYY-MM-DD at the source. Never let the interpretation depend on the server's configuration.

SELECT 'Extra virgin olive oil'::VARCHAR(5); returns Extra. No error and no warning. And here's the inconsistency worth knowing about: if you try to insert that same string into a VARCHAR(5) column, PostgreSQL does fail with value too long for type character varying(5). The explicit conversion truncates; the insertion doesn't. It's the only PostgreSQL conversion that loses data silently. Finally, the usual warning: a WHERE order_date::TEXT LIKE '2025%' wraps the column in a conversion and can't use the index, just like 06-01's functions (08-03).

When the format isn't the standard one, the to_* functions take a pattern and do the conversion by your rules, not the engine's.

The G (thousands) and D (decimal) symbols in numeric patterns depend on lc_numeric: on an English server they produce 1,234.50 and on a Spanish one, 1.234,50. If you need a result that's independent of the server, use the literal , and . in the pattern or normalise with REPLACE. TO_CHAR over numbers completes the pair with TO_CHAR over dates from 06-03: the same function, different patterns.

COALESCE(a, b, c, …) returns the first of its arguments that isn't null, and NULL only if they all are. It takes any number of arguments.

The third column deserves attention: 1/0 would raise a division-by-zero error… if it were ever evaluated. COALESCE is lazy: as soon as it finds a non-null argument it stops looking at the rest, which lets you put expensive or dangerous calculations as a last resort. And one requirement that surprises people: all the arguments have to be of compatible types, so COALESCE(employee_id, 'Web sale') fails because employee_id is an integer.

This is what 04-03 left outstanding: the ten web orders with employee_id IS NULL.

Here 06-01's circle closes. CONCAT_WS ignores nulls, so for the web orders it returns the empty string and not NULL; and COALESCE doesn't replace it, because '' isn't null, so channel_wrong comes out empty. The combination COALESCE(NULLIF(x, ''), 'Web sale') is the correct form and the idiom to memorise: NULLIF turns the empty string into a null and then COALESCE can do its job. If you concatenate with || instead of CONCAT_WS, the null propagates and COALESCE(e.name || ' ' || e.last_name, 'Web sale') works directly; both routes are valid, what isn't valid is mixing them without noticing.

The same pattern with the seven customers with no referrer (04-03), this time with ||:

Important: COALESCE is a presentation tool, not an analysis one. Replacing a null with a piece of text is fine for a report a person reads; replacing it with a 0 inside a calculation changes the result, and that's section 8.

NULLIF(a, b) returns NULL if a = b, and a otherwise. It's exactly the opposite of COALESCE: instead of removing nulls, it manufactures them.

It looks useless until you see what it's for. It has two canonical cases and practically no others.

Remember 04-03's warning: you can't protect yourself with AND stock <> 0, because the planner reorders the conditions. NULLIF does protect you, because it acts inside the expression:

Product 13 has stock 0: without NULLIF, that row would raise ERROR: division by zero and the whole query would fail. With NULLIF(p.stock, 0) the denominator becomes null, the division returns NULL and the report honestly says "can't be computed", which is the truth: the turnover of a product with no stock isn't zero, it's undefined.

It's the second-surname problem left open in 06-01:

Many legacy systems store '' where they should store NULL —web forms with empty fields, CSV imports—. NULLIF(column, '') is the standard cleanup; in a sanitising UPDATE it would be SET city = NULLIF(TRIM(city), '').

COALESCE is syntactic sugar over a CASE. These two expressions are exactly equivalent:

The rule is simple: if the question is "is it null?", COALESCE; if it's anything else, CASE — the next lesson. Writing CASE WHEN x IS NULL THEN 0 ELSE x END isn't wrong, but it's four times longer than COALESCE(x, 0) and it hides the intent.

Here COALESCE stops being cosmetic and changes the result. In 04-04 you learned that aggregate functions ignore nulls; let's see what happens if you hand them zeros instead. The average rating of the products, joining the catalogue's 20 rows with the 12 reviews:

4.08 against 2.13. The same data, two answers that look nothing alike. AVG(r.rating) divides 49 by 12: the average of the reviews that exist, the answer to "what do the customers who have given an opinion think?". AVG(COALESCE(r.rating, 0)) divides 49 by 23, counting the 11 products with no review as if they'd been given a zero.

The first is almost always the correct one, because "nobody has given an opinion" isn't "everybody gave a bad one". The second is exactly the error 04-03 described when talking about sentinel values: inventing data so you don't have to handle its absence.

The same effect with salaries, joining each order with its sales rep:

The rule: use COALESCE outside the aggregate to present (COALESCE(SUM(x), 0)) and inside only when the zero is a real value, not a gap. The question to ask is: "does that gap mean zero, or does it mean there's no data?".

The exact opposite case. SUM over zero rows doesn't return 0, it returns NULL:

The first five add up to €727.95, the canonical figure. The Supplements category only contains product 20, discontinued and never sold: SUM over an empty set gives NULL. And here COALESCE(…, 0) is correct, because "nothing has been sold" is exactly zero euros. The difference from the reviews case is one of meaning, not of syntax.

Three warnings. SQL Server's ISNULL isn't IS NULL: they're different things and they're written almost the same. In Oracle, '' is NULL, so NULLIF's use 2 is redundant there… and all the code that depends on telling them apart stops working when you migrate. And PostgreSQL has no TRY_CAST: for a tolerant conversion you have to validate beforehand with a regular expression (text ~ '^[0-9]+$', from 04-01) or write a function of your own (module 10).

• Trusting implicit conversion. '10' > '9' is false. Convert it yourself and what you mean will be written down.
• Expecting NUMERIC::INTEGER to truncate. It rounds: 12.9::INTEGER is 13. To truncate, TRUNC.
• Converting to VARCHAR(n) without counting the characters. It's the only PostgreSQL conversion that loses data silently.
• Letting '03/04/2025'::DATE decide for you. It depends on DateStyle. Use TO_DATE with a pattern, or ISO at the source.
• Believing COALESCE fixes empty strings. '' isn't null: the correct idiom is COALESCE(NULLIF(x, ''), 'value'). And every argument has to be of a compatible type.
• Putting COALESCE(col, 0) inside an aggregate without thinking. It changes the denominator: 4.08 became 2.13. Only if the zero is a real value.
• Forgetting COALESCE(SUM(x), 0) in reports. SUM over zero rows returns NULL, not 0, and the cell comes out empty.
• Protecting yourself from division by zero with an AND. The planner reorders. NULLIF(divisor, 0) does protect you.
• Applying a CAST to a column in the WHERE. Just like a function: it prevents the index from being used (08-03).
• Tip: normalise on the way in, not in every query. If the CSV brings '12,50', fix it on loading; don't scatter REPLACE across fifty reports.
• Tip: COALESCE to present, never to compute; and if you do compute with it, write a comment saying why the zero is legitimate.
• Tip: use CAST instead of :: in code you're going to publish or port. It costs five more characters and it works everywhere.

Prepare the order listing for management: id, date, status, customer name and a handled_by column with the sales rep's full name or the text 'Web channel' when there isn't one. Add a method column with the payment method in upper case, show orders 15 to 20 and check that no cell is left empty.

Over products, compute the defensive percentage margin —(price - cost) / price * 100 to two decimals— so that the query never fails even if one day price is 0 or cost is NULL. Show the cost with a COALESCE to 0.00 and explain why that particular substitution is debatable.

A colleague presents this satisfaction report and concludes that "the catalogue's average rating is 2.13 out of 5, a disaster":

(1) Where does the 2.13 come from and why is it misleading? (2) Write the correct query and give the figure. (3) Write a third one giving both useful metrics at once: the average of the rated products and how many products have no review at all.

Solution 1

The JOIN with customers can be an inner one because customer_id is NOT NULL; the one with employees has to be a LEFT JOIN, or you'd lose half the orders (03-03). And the NULLIF is essential: without it, the three web rows would show an empty cell instead of 'Web channel'.

Solution 2

NULLIF(price, 0) shields the division and 100.0 avoids 06-02's integer division. But the COALESCE(cost, 0) is debatable, very much so: an unknown cost isn't a cost of zero euros. With that substitution, a product whose supplier hasn't yet communicated the purchase price would appear with a 100 % margin, the most optimistic possible figure and the most false. The honest thing is to let the margin come out NULL —(price - cost) already does that on its own— and have the report show "no data".

Solution 3

1. The LEFT JOIN produces 23 rows: the 12 reviews plus one manufactured row for each of the 11 products with no review. COALESCE(r.rating, 0) turns those 11 absences into eleven zeros, so the average is 49 / 23 = 2.13. It's misleading because a product with no reviews hasn't received a zero: it hasn't received anything. The report measures the lack of reviews, not satisfaction.

2. The correct query is the one that lets AVG do what it's been doing since 04-04 —ignore nulls— and returns 4.08 out of 5, module 4's canonical figure. It isn't a disaster: it's a good rating. 3. And the two metrics separated, each answering its own question:

Now the report tells the whole truth: the rated products score 4.08 on average, but 11 of the 20 have no review at all. That second number is GreenStore's real problem, and it was hidden inside the 2.13. When a COALESCE blends two questions into one figure, the solution isn't to refine the figure: it's to separate the questions.

You now have the glue:

• You can tell implicit conversion —which the engine performs without warning and which can return something else, like '10' > '9' being false— from explicit conversion, which you write and which stays documented. You convert with CAST(expr AS type) (standard) or expr::type (PostgreSQL), knowing that :: is applied before the arithmetic.
• You know the traps: non-numeric text produces an ERROR (and that's good), NUMERIC::INTEGER rounds instead of truncating, '03/04/2025' depends on DateStyle and the conversion to VARCHAR(n) truncates silently. And you control the format with TO_NUMBER, TO_CHAR and TO_DATE when the standard isn't enough.
• You replace nulls with COALESCE, which is lazy, takes several arguments and demands compatible types; and you manufacture nulls with NULLIF, whose two uses are avoiding division by zero and treating '' as null.
• You've memorised the idiom COALESCE(NULLIF(x, ''), 'value'), which definitively closes the || and CONCAT_WS problem you'd been carrying since 02-02. And you know COALESCE is equivalent to a CASE, and when to use each.
• And above all, you know that COALESCE inside an aggregate changes the result: 4.08 became 2.13 by counting as zeros eleven products nobody had rated. COALESCE outside the aggregate presents; inside, it decides.

One last piece is missing. COALESCE only knows how to answer one question —"is it null?"— and all the others are still out of your reach: classifying a product as "budget", "mid" or "premium" according to its price; putting a traffic light on the stock; sorting an order's statuses by their flow order and not alphabetically; or turning the rows of a GROUP BY into the columns of a report. That needs conditional logic inside the query, and it's what the module's last lesson brings: CASE, conditional expressions.