Data Types: Intermediate

A BOOLEAN column has 3 possible values. TRUE and FALSE are recorded answers. NULL means the system never recorded an answer. Take an email_verified flag on a users table. Rows written after the verification feature shipped carry TRUE or FALSE. Rows written before it carry NULL, because nobody asked the question back then. Reading that NULL as FALSE would flag long-time customers as unverified, and reading it as TRUE would skip a security check. The database does neither. It keeps the unknown unknown, and every operator that touches it follows rules that fit in a truth table.

The users table below has 6 rows. email_verified and phone_verified are its 2 flags, and across the rows they cover every combination of the 3 BOOLEAN values. The query computes 3 new BOOLEAN columns from them. Before you run it, predict the row for user 3, where email is FALSE and phone is NULL.

A comparison is an expression whose type is BOOLEAN. country = 'US' yields TRUE for a US row and FALSE for a Canadian one. When country is NULL the comparison yields NULL, because an unknown country might or might not be the US. That is why country = NULL never finds anything: comparing against an unknown returns unknown for every row, including the rows where the column really is NULL. The operator built for that question is IS NULL. It inspects the slot rather than the value, so it returns TRUE or FALSE and never NULL, and IS NOT NULL gives the opposite.

Account 3 returns NULL for both is_us and is_not_us, so a row can fail the US test and fail the non-US test at the same time. Only country_missing returns TRUE for it. Any time a report splits rows into 2 buckets with a comparison, a third bucket of NULL rows is sitting between them, and it stays invisible until a query tests for it by name.

The pennies table stores 3 pairs of amounts as text, the way a CSV loader would leave them. The query adds each pair twice, once after casting to DOUBLE and once after casting to DECIMAL(10,2). Predict the first row before running.

order_lines stores unit_price and tax_rate as text and quantity as a whole number. The CTE casts each column once, and the outer query builds the subtotal, the raw tax with its full 6 places, and the tax rounded to cents.

A TIMESTAMP is a reading off a wall clock with the clock's location left out. 2024-03-15 09:00:00 says a clock showed 9 in the morning, and nothing about which clock. A warehouse in Tokyo and a warehouse in New Jersey both record 2024-03-15 09:00:00 for a morning pickup, and those 2 pickups are 13 hours apart. TIMESTAMP WITH TIME ZONE, which this section's title and many engineers shorten to TIMESTAMPTZ, keeps the clock's location with the reading: 2024-03-15 09:00:00 Asia/Tokyo. With the zone attached, the value names one instant on the global timeline, and 2 such values can be ordered or subtracted no matter where they were recorded.

The shipments table records every pickup twice. pickup_local is what the warehouse clock showed. pickup_stamped is the same reading with the warehouse's zone appended. Both are stored as text, so each query casts the column it needs. The 2 tabs below differ in which column they cast and sort by. Pick the order you expect from each before you switch.

Every example in this lesson and the next section uses a date in March 2024, so the offsets above are the ones to use when predicting a result.

AT TIME ZONE 'UTC' takes a TIMESTAMP WITH TIME ZONE and returns the same instant expressed in the zone named, so the digits change and the point on the timeline stays where it was. The logins table below holds 4 readings from 4 zones as text. The animation casts each one and converts it to UTC; watch how the row with the latest clock reading on March 15, Sydney at 23:40, becomes the earliest instant of the 4.

Stored in UTC, an event converts to any zone on demand, and the same AT TIME ZONE does the job in the other direction. The payments table keeps paid_at in UTC. The query shows each payment as the Chicago finance team and the Tokyo team would see it. Payment 2 is the one to watch: it moves to a different calendar day in Chicago.

A plain TIMESTAMP cannot be converted on its own, because AT TIME ZONE needs to know which zone the reading came from before it can move it. The repair takes 2 steps: attach the zone the clock was in, then convert. with_timezone does the first step. You give it a TIMESTAMP and a zone name, and it returns a TIMESTAMP WITH TIME ZONE with the digits unchanged. Which zone to attach is a fact about the source system, found in its configuration or its documentation, and never in the data itself.

Mixing 2 types in 1 expression forces the engine to pick a common type, and for numbers it does so without being asked. An INTEGER added to a DOUBLE becomes a DOUBLE first. An INTEGER multiplied by a DECIMAL is treated as a DECIMAL with scale 0, so the CAST(quantity AS DECIMAL(5,0)) in the tax example could have been left out and the engine would have promoted the INTEGER the same way. These silent promotions are implicit casts. They run toward the type that can hold the wider range, and range is a different property from exactness: a BIGINT, the 64-bit whole-number type, loses its last digits above 2^53 when promoted to DOUBLE, and a DECIMAL mixed with a DOUBLE becomes a DOUBLE, so DECIMAL 0.1 plus DOUBLE 0.2 gives 0.30000000000000004. A single DOUBLE column in a money calculation turns the whole result into an approximation. The promotion that bites people most often is the one that never happens: 2 INTEGER values divided give an INTEGER, and the fractional part is discarded.

Campaign spring_sale has 37 clicks and 120 views. The integer ratio is 0, because 37 divided by 120 is 0 with 37 left over, and INTEGER keeps the whole part. Once either side is DOUBLE the whole expression is DOUBLE and the result is 0.308333 with a tail of digits. The DECIMAL version rounds to the 4 places of its scale, 0.3083, so the type you cast to decides the digits a reader sees. Campaign retarget with 600 clicks over 400 views comes out 1, 1.5 and 1.5000: the integer version keeps the 1 and drops the half.

Text never promotes. '5' + 1 fails with a type error, because the engine will not guess whether the text holds a number, and the same error appears when a VARCHAR column meets an INTEGER in a comparison. That refusal protects you from a column of ids where most rows look numeric and 1 row holds N/A. You already know CAST. The other explicit tool, TRY_CAST, has the same syntax and 1 different behavior: when a value cannot become the target type, TRY_CAST returns NULL for that row and the query keeps running.

raw_signups is a feed where every field arrived as text and some rows were typed by hand. Each column below is converted to the type it should have had. Rows 2 and 3 carry the damage; predict which of their cells come back NULL and why. The last column applies IS NULL to the converted age to flag a value that failed.

Row 2 loses every conversion. thirty holds no digits, February 30 is a date that does not exist, maybe is not on the short list BOOLEAN accepts (the words true and false or their first letters in any case, plus 1 and 0), and 1200.00 needs 6 digits where DECIMAL(5,2) allows 5. Row 3 is subtler. 27.5 fails as an INTEGER because the conversion demands a whole number and refuses to round, and 03/15/2024 fails because the TIMESTAMP parser expects the date written year first with dashes between the parts. Its 1 does convert, to TRUE. Row 4 shows that an empty string becomes NULL as an INTEGER, while the same empty string as a VARCHAR is a real value that holds no characters.