Values, missing data and equality

A SQL type determines which values a column can hold and what operations mean for them. The shop’s product code P7, price 20.00 and order time may all arrive from an application as text. They describe different things: an identity, an amount and a moment. Treating them alike loses distinctions the application needs.

The choice shows up later in a calculation or comparison. Dividing integers can discard a fraction before it reaches a decimal column. Two different clock readings can be the same moment. Two strings can compare equal under one rule and different under another. And when a value is missing, a test need not be either true or false.

Every example starts from the small tea shop introduced in Relational databases. P7 is a blue mug priced at 20.00. Order 103 was placed at 10:15 UTC on 20 October 2026. The SQL and engine-specific rules below are PostgreSQL 18’s; the questions about meaning apply wherever the data is stored.

Types, values and their written forms

A written form is how a value enters or leaves the database. It is not necessarily the value’s identity. The code P7 is compared to find a product; its price 20.00 is used in arithmetic; the order time locates an event. Receiving all three as strings does not make text the right storage type for all three.

In SQL, '20.00' is a quoted string literal whose eventual type can be supplied by context. numeric '20.00' gives it an explicit type. A cast converts a value to another type: PostgreSQL’s '20.00'::numeric is the same request as CAST('20.00' AS numeric). Conversion may reject input, round it or discard information; it is more than changing a label.

The type also leaves business rules open. An integer permits negative counts, and a decimal does not say which currency an amount uses. The shop’s prices have no assigned currency in this example. A real application must record or otherwise fix the monetary unit before adding amounts together. Constraints add rules such as a non-negative stock count; a type alone cannot express every valid fact.

Numbers, money and rounding

Counts such as stock and order quantity are whole numbers, so an integer fits. For amounts that must add up exactly in decimal, use an exact decimal type such as numeric, or an integer with an explicitly named unit such as hundredths of the monetary unit. Floating-point types such as double precision store approximations. They suit measurements and calculations whose allowed error permits that approximation.

The distinction is in the arithmetic, even when the display hides it. In PostgreSQL, 0.1::numeric + 0.2::numeric = 0.3::numeric is true. The corresponding double precision comparison is false. Rounding the displayed answer to one decimal place can make both look like 0.3; it does not make the underlying values equal.

Conversion can change the answer

Suppose we divide P7’s amount of 20 into three shares. With integer inputs, 20 / 3 produces 6: integer division discards the fraction. Casting that result to numeric still gives 6, because conversion cannot recover information already lost. In 20::numeric / 3, the conversion happens first, so division uses decimal arithmetic and returns approximately 6.6667.

SELECT (20 / 3)::numeric AS converted_after,
       20::numeric / 3   AS converted_before;
-- converted_after: 6
-- converted_before: 6.6666666666666667

PostgreSQL can convert inputs implicitly too: 20 / 3.0 uses numeric division because the decimal literal changes the expression’s types. Make the conversion explicit, or supply correctly typed parameters, when the calculation depends on it. The same problem crosses the application boundary: converting an approximate number to decimal cannot reconstruct an original exact amount that was already lost.

Precision and scale

The shop declares prices as numeric(8,2). The precision, 8, allows eight decimal digits in all; the scale, 2, keeps two after the decimal point. For finite values that leaves six before it, from −999999.99 to 999999.99. The price’s separate CHECK (price >= 0) rules out negative amounts.

PostgreSQL first rounds a value to the column’s scale, then checks whether it fits. 20.005::numeric(8,2) becomes 20.01. 999999.995::numeric(8,2) rounds beyond the available six digits and fails. Formatting 20 as 20.00 for a screen is a different operation: it need not change the stored value.

Exact decimal storage still leaves a rounding decision. There is no two-decimal amount equal to one third of 20.00. Rounding each share independently gives 6.67 + 6.67 + 6.67 = 20.01, one hundredth more than the starting amount.

Allocating 6.67, 6.67 and 6.66 instead preserves the 20.00 total. Choosing who receives the smaller share is a business rule; decimal storage does not choose it. This is a separate allocation example, not a payment added to the shop.

Decide where rounding belongs: per item, per tax line, per order, or when an amount is settled. Different points can give different totals. Storing 20.00 as 2,000 hundredths avoids decimal storage rounding, but dividing 2,000 by 3 still leaves a remainder. Scaled integers need a named unit, enough range for intermediate calculations and a division policy just as decimals do.

Dates, local times and instants

A calendar date, a local clock reading and a moment are different facts. Use date for a day such as a product’s best-before date. Use timestamp without time zone for local date-and-time fields whose meaning does not yet identify an instant. Use timestamptz, PostgreSQL’s short name for timestamp with time zone, for the instant an order was placed.

Order 103’s placement time, 20 October 2026 at 10:15 UTC, is the same instant as 12:15 at offset +02. PostgreSQL converts a timestamptz input to UTC and retains that instant, not the input’s zone name or original offset. Displaying it in another zone changes the clock reading, not when the order happened.

SELECT timestamptz '2026-10-20 10:15+00'
     = timestamptz '2026-10-20 12:15+02' AS same_instant;
-- true

An explicit offset says how a clock reading relates to UTC. If a timestamptz input has none, PostgreSQL interprets it in the session’s TimeZone. Output uses that zone too. A bare timestamp means timestamp without time zone; adding +02 inside its literal does not turn it into an instant. PostgreSQL ignores that offset for this type.

Time zones and calendar arithmetic

A named zone such as Europe/Stockholm supplies rules for converting local clocks on particular dates. A fixed offset such as +02 does not describe those rules. When clocks move back, a local time can occur twice; when they move forward, some local times never occur. A scheduling system needs a policy to resolve such cases. PostgreSQL has its own documented resolution rules, so accepting an ambiguous input does not establish that it meant what the user intended.

Calendar arithmetic also needs a zone. Start at midnight on 25 October 2026 in Stockholm, at offset +02. During that date the clock moves back one hour. The next midnight is 26 October at offset +01, so reaching it takes 25 elapsed hours. Adding 24 elapsed hours instead reaches 23:00 on 25 October. A calendar day and a fixed duration answer different questions.

SET TIME ZONE 'Europe/Stockholm';
SELECT timestamptz '2026-10-25 00:00+02' + interval '1 day'
         AS next_calendar_day,
       timestamptz '2026-10-25 00:00+02' + interval '24 hours'
         AS after_24_hours;
-- next_calendar_day: 2026-10-26 00:00:00+01
-- after_24_hours:    2026-10-25 23:00:00+01

Deriving a business date from an instant is another zone choice. In PostgreSQL, placed_at::date uses the session zone; converting a local timestamp to timestamptz, or comparing the two types, can depend on it too. State the intended zone in the expression:

SELECT order_id,
       (placed_at AT TIME ZONE 'Europe/Stockholm')::date
         AS business_date
FROM orders;
-- Order 103: 2026-10-20

AT TIME ZONE here produces the local date-and-time fields for that instant in Stockholm; the cast then takes their date. The zone is a choice for this example, not a property of the shop’s order row. If the original zone matters as a fact, store it separately.

Text, collation and equality

Text preserves characters. A collation supplies rules used to compare and order those strings. The name shown to a customer and the rule used to find it need not be the same representation: you can preserve a name’s spelling while searching in a way that ignores selected differences.

The following pairs make four separate choices visible. The code pairs are candidate inputs to compare, not extra products in the shop.

In PostgreSQL, a deterministic collation breaks comparison ties using the bytes, so different byte sequences remain unequal. A deliberately configured nondeterministic collation can make selected differences insignificant. Which ones depends on its provider and settings: choosing a locale does not by itself mean case-insensitive equality. Such a rule can affect a unique key as well as a search.

For this shop, suppose product codes must match their stored spelling exactly. A deterministic comparison keeps P7 and p7 distinct. If the input policy instead accepts lower-case ASCII codes, canonicalise them consistently before storing and looking them up, then enforce uniqueness under the same policy. Applying lower() somewhere in a query is not a complete policy for every language, accent or Unicode normal form.

Ordering is a policy too. PostgreSQL’s COLLATE "C" orders text bytewise: among ASCII strings, P10 sorts before P7 because 1 precedes 7. It does not interpret the suffixes as quantities. A person’s alphabetical list may need a locale-aware order; a machine code may need a different one.

Use a length limit when the domain supplies one, such as a protocol’s maximum code length. PostgreSQL’s text has no declared length limit, and varchar(n) limits characters, not bytes. char(n) pads with spaces and disregards trailing spaces in comparisons; text and varchar treat those spaces as significant under deterministic comparison. Choosing a fixed-length type for a varying name can therefore change equality as well as storage.

Identifiers are values with a purpose

A product code identifies a product; it is not an amount. A code made only of digits can still require text if leading zeroes matter. Converting '007' to an integer gives 7 and loses that distinction. The shop’s customer_id, by contrast, is an integer used to reference a customer. Adding two customer IDs produces no useful customer fact.

PostgreSQL’s uuid type stores a 128-bit identifier and accepts UUIDs generated inside or outside the database. An identity column generates integers from a sequence. Neither declaring a UUID type nor declaring GENERATED ... AS IDENTITY replaces a PRIMARY KEY or UNIQUE constraint.

Generation and meaning are separate. Two IDs can belong to duplicate customer records; detecting that needs a business definition of “the same customer”. An increasing ID is not a promise about event or commit order. A difficult-to-guess ID is not permission to read the row: the application must still authorise access. Relational data modelling develops which facts and keys identify a row.

When one value has parts

The label Green tea, 50 g has punctuation and a number. That alone is no reason to split it. The reason appears when the shop wants to compare pack sizes, convert units or change the name without changing the pack quantity. Separate values for the name Green tea, amount 50 and unit g make those operations possible without parsing the label. Formatting them can still produce the same words for a customer.

SQL can hold structured values: arrays, composite values and JSON are all available in PostgreSQL. One column need not be one indivisible string. A pack size could be represented by separate columns or by a structured value, depending on the operations and validation it needs.

A JSON value such as {"amount":50,"unit":"g"} has parts, but valid JSON syntax does not establish a valid pack size. {"amount":"large","unit":"perhaps"} is valid JSON too. You still need rules for the amount’s type, permitted units and missing members. Choose the representation from what must be compared, validated and updated together. Independently repeated facts and relationships raise the modelling questions developed in Relational data modelling.

NULL and missing information

Now add a package weight for each product, in grams. P3 is 65 g and P9 is 820 g. P7 has not yet been measured, so its package_weight_g is NULL. These invented measurements form a separate scenario; they do not change the shop’s prices or orders.

NULL marks absent information. It is distinct from the known weight zero, from an empty string, and from having no product row at all. Here it has one precise meaning: not yet measured. If the application also needs to distinguish “not applicable” or “measurement failed”, represent those reasons explicitly, for example with a measurement status. A single NULL cannot tell you which reason applied.

True, false and unknown

A predicate is a test whose result can select a row. package_weight_g >= 100 is false for P3 and true for P9. For P7 the weight is absent, so the result is unknown, represented by a null Boolean. It might pass when measured, or it might not.

WHERE keeps only rows for which its predicate is true. Negating the weight condition does not turn P7’s unknown measurement into a known result: NOT unknown is still unknown.

The first filter keeps P9. Negating it keeps P3. P7 appears in neither result: NOT unknown is still unknown. So “the rows that pass” and “the rows that pass the negated test” need not divide all the input rows between them.

AND and OR follow the same reasoning. False AND unknown is false because one failed condition is enough to rule out both being true. True OR unknown is true because one satisfied condition is enough. When neither known result settles the answer, it stays unknown.

The condition package_weight_g >= 100 OR product_id = 'P7' keeps the mug on its code alone: unknown OR true is true. To ask about absence directly, test package_weight_g IS NULL. IS NULL returns true for P7 and false for the others; IS NOT NULL does the reverse.

The same logic explains a surprising NOT IN. Test the known weight 65 against a list containing 820 and NULL:

SELECT 65 IN (820, NULL)     AS is_in,
       65 NOT IN (820, NULL) AS is_not_in;
-- is_in: NULL (unknown)
-- is_not_in: NULL (unknown)

IN combines equality tests with OR: 65 equals 820 is false, and 65 equals NULL is unknown. False OR unknown gives unknown. NOT IN negates that result, leaving it unknown too. A filter using either expression keeps no row. By contrast, 65 IN (65, NULL) is true because one equality succeeds. Decide what a null list member means before using the list as an exclusion rule.

Comparing and replacing missing values

Ordinary equality cannot establish that two absent measurements match. NULL::integer = NULL::integer is unknown, just as comparing a known weight with an absent one is unknown. Do not write weight = NULL to find missing values; use weight IS NULL.

Sometimes the question is whether two fields contain the same recorded information, including both being absent. IS NOT DISTINCT FROM expresses that comparison. It is true for two equal known values or two NULLs, and false when just one is NULL. It does not establish that two unmeasured objects have the same actual weight.

SELECT NULL::integer = NULL::integer AS ordinary_equality,
       NULL::integer IS NOT DISTINCT FROM NULL::integer
         AS same_recorded_information;
-- ordinary_equality: NULL (unknown)
-- same_recorded_information: true

COALESCE returns its first non-null argument. That is useful for a display label:

SELECT product_id,
       COALESCE(package_weight_g::text, 'Not yet measured')
         AS weight_label
FROM products;
-- P3: 65     P7: Not yet measured     P9: 820

COALESCE(package_weight_g, 0) would instead supply a numeric zero. Used in a shipping calculation, it would turn an unmeasured mug into a weightless mug. Choose a fallback only when it has the intended meaning. Likewise, order 103’s lack of payment rows is a different absence from this missing attribute. Joins and the meaning of a result row follows missing matches into query results.

Equality depends on the operation

The shop can also assign optional loyalty codes. Mina has TEA-7; Tariq and Edda have no assigned code. PostgreSQL’s default UNIQUE constraint permits both NULLs while refusing a second TEA-7. Ask for NULLS NOT DISTINCT and the second absent code conflicts too.

Neither rule changes what NULL = NULL means in a predicate. Duplicate elimination has its own rule too: SELECT DISTINCT loyalty_code returns one NULL for the two absent codes. The operation determines how absence participates, so do not infer uniqueness behaviour from an equality test. Defaults differ between database engines.

CHECK makes another distinction. PostgreSQL accepts a row when the check is true or unknown. CHECK (package_weight_g > 0) rejects a measured zero but allows the unmeasured mug. Add NOT NULL if every row must have a measurement. That would deliberately reject P7’s current scenario state.

Finally, sorting places NULLs rather than comparing their missing contents. Write ORDER BY package_weight_g ASC NULLS LAST, product_id to put P3, then P9, then the unmeasured P7. PostgreSQL’s ascending default puts NULLs last and descending puts them first; stating the placement makes the intended report clearer.

The useful question is always what distinction the application needs to preserve. A missing measurement, an absent loyalty code and an order with no payment are three different facts. Give each an explicit meaning, choose the type and comparison rule that preserve it, and carry that meaning into calculations and filters.