Skip to main content

PG.L5.MONEY_TYPE — The type called money is not the one you want

  • Category: safety
  • Level: 5
  • Confidence: deterministic — the type is the finding, with no name heuristic at all
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

Three problems, and the first one is silent​

Its input and output depend on a server setting. money is parsed and rendered through lc_monetary. A dump taken where the locale writes 1.234,56 and restored where it writes 1,234.56 does not fail — it reads the same characters as a different number. No error, no warning, just a different amount. The only evidence is the amount itself.

It carries no currency. The name promises otherwise, and a money column called price reads as though it knows what currency it is in. It does not: the currency lives in a locale setting that is a property of the server, not of the row. Two applications on one instance cannot disagree about it.

Its scale is fixed by that same setting. How many fractional digits survive is not a property of the column, so two servers can disagree about how much of an amount to keep.

Flagged​

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->addColumn('money', 'invoice_total');
});

Preferred​

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->decimal('invoice_total', 19, 4);
    $table->char('currency', 3);
});

The currency goes in a column of its own, where it is a property of the row and travels with it.

Why this is not folded into the float rule​

PG.L5.FLOAT_MONEY answers a different question. It needs a name heuristic, because nothing in a catalog says a column holds money — here the type is the whole finding, with no guessing at all, which is why this rule's confidence is deterministic and that one's is heuristic.

They also fail differently: a float loses cents to arithmetic, money changes meaning when the server does. A project may reasonably accept one and not the other, and separate ids are what makes that possible — under a single id, an ignore entry for one would silence the other.

The recommendation is nonetheless the same for both, and saying so plainly is more useful than pretending two roads lead to two places.

An array is covered; a domain is not​

Both measured on 18.4, because the answer is not obvious in either direction.

money[] is reported. The catalog spells it money[], and the canonicalizer strips the array marker from the name while the raw spelling keeps it — so the column arrives at the rule as money. An array of amounts carries exactly the same locale problem, and a silent miss there would have looked like a clean table.

A domain over money is not, and cannot be. CREATE DOMAIN eur AS money produces columns whose type the catalog reports as eur. Nothing in the rule can see the money underneath — and there is no undetermined to emit either, because the rule has no way to tell a domain from an ordinary user type it correctly has nothing to say about. The limit is stated here, and a live test asserts it, so it stays a documented boundary rather than a surprise.

What is not flagged​

Only money columns are reported, and every other type is somebody else's business or nobody's. A legacy schema whose reporting genuinely depends on the server locale is the one real reason to keep the type — record that on the ignore list, where the decision stays visible and the finding stays counted:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'PG.L5.MONEY_TYPE', 'objects' => ['public.legacy_ledger']],
        ],
    ],
],

Changing the type rewrites the table​

Converting to numeric(19, 4) rewrites every row. The lint suite classifies that operation and carries its downtime class; this audit finding carries none, because an observation about a state has no duration.

Sources​

  • PostgreSQL 18: the money type — output depends on lc_monetary and input is interpreted under that same setting, so the value is locale-dependent; the documentation recommends numeric where that dependency is unwanted, and notes the fractional precision is determined by the locale.