Bedragen in PostgreSQL: numeric, integer of money?

In bedragen in centen, geen floats leggen we uit waarom een komma-getal geen geld kan bewaren. Dan blijft de praktische vraag over die elk PostgreSQL-schema moet beantwoorden: welk type gebruik je dan wél? Er zijn drie serieuze kandidaten — numeric, een geheel aantal centen in integer of bigint, en het ingebouwde type money — en de discussie erover is meestal luider dan het verschil.

Dit artikel zet ze naast elkaar, met het gedrag dat we zelf in psql hebben nagelopen en met wat onze eigen twee producten werkelijk gebruiken. Dat laatste doet ertoe: wij bewaren niet elk bedrag in centen, en we leggen uit waarom.

Welk PostgreSQL-type gebruik je voor bedragen?

Voor de meeste SaaS-producten is numeric met een expliciete precisie en schaal het juiste type voor geld — numeric(14,2) voor bedragen, en meer decimalen voor stukprijzen en tarieven — omdat het exact is, SQL-rapportages er direct mee kunnen rekenen en elke kolom het aantal decimalen krijgt dat hij nodig heeft. Een geheel getal in de kleinste munteenheid (bigint in centen) is net zo exact en sneller, en past beter als bedragen alleen worden opgeteld en vooral tussen applicatiecode en een betaalsysteem heen en weer gaan. Gebruik geen real of double precision, want die zijn niet exact, en geen money, want dat kan geen fracties van een cent bewaren en koppelt zijn decimalen en opmaak aan de database-instelling lc_monetary.

De documentatie van PostgreSQL zegt hetzelfde over numeric: het wordt “especially recommended for storing monetary amounts and other quantities where exactness is required”.

De typen in één tabel

De kolom die het vaakst beslist is fracties van een cent. Zodra je een stukprijs van € 0,0125 hebt, een brandstofprijs per liter of een btw-tarief van 21,000, hebben centen een tweede regel nodig voor die getallen, en numeric niet.

numeric: exact, en hij rondt af zonder het te zeggen

numeric bewaart decimale cijfers in plaats van machten van twee, dus 0.1 + 0.2 is gewoon 0.3. Optellen, aftrekken en vermenigvuldigen zijn exact. De prijs is snelheid: volgens de PostgreSQL-documentatie zijn berekeningen met numeric “very slow compared to the integer types”. Voor een SaaS-product dat een paar duizend factuurregels optelt, merk je daar niets van; voor analyses over honderden miljoenen rijen kan dat anders zijn.

Drie dingen die we op PostgreSQL 16.14 hebben nagelopen, en die je wilt weten voordat je erop leunt:

• Een kolom met een schaal rondt af bij het opslaan. 2.345 in een kolom numeric(12,2) wordt 2.35, zonder waarschuwing en zonder fout. De documentatie bevestigt het: heeft de waarde meer decimalen dan de kolom, dan “the system will round the value”. Een btw-bedrag dat de applicatie met vier decimalen uitrekent, komt dus al afgerond in de database — één keer, op een plek die niemand heeft gekozen.
• numeric zonder precisie en schaal accepteert elk aantal decimalen. De documentatie noemt dat een “unconstrained numeric”-kolom. Dan houdt niets een bedrag van 10.3333333333333333 tegen. Geef een geldkolom dus altijd een schaal.
• NaN past erin. numeric kent de speciale waarden NaN, Infinity en -Infinity. In onze test weigerde een numeric(12,2) wel Infinity, maar accepteerde hij 'NaN'. Een bedragkolom verdient een CHECK-constraint die dat uitsluit.

En ook numeric lost het delen niet op. 10.00 / 3 geeft 3.3333333333333333; rond je elk van de drie delen af op centen en tel je ze op, dan kom je op 9.99. De verdwenen cent is geen typeprobleem maar een verdeelprobleem, en de oplossing — naar beneden verdelen en de resterende centen uitdelen aan de grootste resten — staat in het artikel over centen.

Centen in een integer: snel, en delen gooit de rest weg

Een geheel aantal centen is exact en zo snel als een getal maar kan zijn, en zolang het onder de grenzen hieronder blijft, past het in een gewoon JavaScript-getal. Er horen drie grenzen bij:

• integer stopt bij € 21.474.836,47. Het maximum van een integer van vier bytes is 2.147.483.647, en in centen is dat ruim 21 miljoen euro. Een maandabonnement haalt dat nooit; een jaartotaal over alle klanten wel. Overschrijden geeft de fout integer out of range — het gaat dus niet stil mis, maar in productie. Gebruik voor totalen bigint.
• bigint past niet in een JavaScript-getal. Number.MAX_SAFE_INTEGER is 9.007.199.254.740.991; daarboven is een JavaScript-getal niet meer exact. Daarom geeft node-postgres een bigint standaard als tekst terug. In centen ligt die grens bij ongeveer 90 biljoen euro, dus het bedrag zelf is veilig — maar code die overal zonder nadenken Number(row.total) doet, heeft de garantie wel losgelaten.
• Delen in SQL kapt af. 1000 / 3 geeft 333, en -1000 / 3 geeft -333: de rest verdwijnt gewoon, volgens de documentatie “towards zero”. Een rapport dat in SQL bedragen verdeelt, kan op elke regel een cent kwijtraken zonder foutmelding.

En een stukprijs met vier decimalen past niet in centen. Dan heb je een tweede kolom met een andere eenheid nodig, of een afspraak welke kolommen in honderdsten van een cent tellen — en dat is precies het soort tweede vorm van een bedrag dat iemand uiteindelijk bij de eerste optelt.

Het type money: waarom de PostgreSQL-wiki het afraadt

money lijkt de voor de hand liggende keuze, en het is het enige van de drie typen waarvan de PostgreSQL-wiki uitdrukkelijk zegt dat je het niet moet gebruiken. Het aantal decimalen “is determined by the database’s lc_monetary setting”, de uitvoer wordt voor die taalinstelling opgemaakt, en volgens de documentatie “might not work” het terugzetten van een dump in een database met een andere lc_monetary. Deel je money door een integer, dan wordt er afgekapt, en money gedeeld door money geeft een double precision — precies het type dat je wilde vermijden.

De wikipagina Don’t Do This is er duidelijk over: money “doesn’t handle fractions of a cent”, en de afronding “is probably not what you want”. Er staat één uitzondering bij: één munt, geen fracties van een cent, en alleen optellen en aftrekken. Weinig SaaS-producten blijven lang binnen die grenzen.

Wat we in onze eigen producten gebruiken

Onze beide producten draaien op PostgreSQL, en geen van beide bewaart al het geld in centen.

booxx, onze Nederlandse boekhouding, bewaart het grootboek in numeric(14,2): debit en credit per journaalregel. Factuurregels hebben een unit_price in numeric(12,4) en een quantity in numeric(12,3), en btw-tarieven staan in numeric(6,3). Een boekhoudproduct heeft verschillende schalen naast elkaar nodig, en het grootboek spreekt dezelfde taal als de rapportages en de aangifte: euro’s met twee decimalen.

De billing-laag van booxx — wat een administratiekantoor per maand ontvangt of betaalt, en wat een abonnement per administratie kost — gebruikt wél hele centen in een integer. De migratie zegt in één regel waarom: bedragen in centen (integer) — dit is de billing-laag, niet de boekhouding. Dat zijn vaste prijzen die alleen worden opgeteld, en daar wint het eenvoudigere type.

Pilot-Next, ons product voor vliegclubs, bewaart factuurtotalen, btw-bedragen en het saldo na elke transactie in numeric(12,2), via Prisma. In de applicatie komen die waarden binnen als Decimal.js-objecten en niet als JavaScript-getallen, zodat de exactheid de stap van database naar code overleeft.

De regel achter beide keuzes: het type volgt wat het bedrag moet doen. Bedragen die worden vermenigvuldigd, verdeeld en met verschillende precisies gerapporteerd, horen in numeric met een expliciete schaal. Bedragen die alleen worden geteld en doorgegeven, horen in hele centen. En in beide gevallen is er in de code één type dat weet dat het geld is.

Controleer je eigen database met één query

Deze query toont de kolommen die een tweede blik verdienen: kolommen met komma-getallen of money, numeric-kolommen zonder schaal, en integers van vier bytes waarvan de naam op een bedrag wijst.

SELECT table_name, column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND (
        data_type IN ('real', 'double precision', 'money')
     OR (data_type = 'numeric' AND numeric_scale IS NULL)
     OR (data_type = 'integer' AND column_name ~ '(amount|cents|price|total|balance)')
  )
ORDER BY table_name, column_name;

Wat je met de uitkomst doet:

• real of double precision met een bedrag erin is het enige echte noodgeval. Elke rij kan al een afwijking bevatten, en later omzetten betekent beslissen welke waarde de juiste was.
• money werkt zolang je binnen één munt blijft en niet deelt. Plan de overstap naar numeric voordat dat niet meer waar is.
• numeric zonder schaal is exact, maar houdt een onafgeronde waarde niet tegen. Geef de kolom een schaal zodra je weet welke.
• integer in centen is prima voor prijzen en abonnementen. Kijk voor totalen en saldi die groeien of 21 miljoen euro echt buiten bereik ligt.

Pas de kolomnamen in de laatste voorwaarde aan op je eigen naamgeving; in een Nederlands schema heten ze misschien bedrag of saldo.

Kun je het type later nog veranderen?

Technisch is het één ALTER TABLE … ALTER COLUMN … TYPE. In de praktijk zit de moeilijkheid niet in de opdracht maar in de data: een kolom double precision omzetten naar numeric(14,2) rondt waarden af die al een fractie verkeerd waren, en bij elke factuur die de deur uit is, is de vraag welk getal klopte — het opgeslagen totaal of de som van de regels. Daarom is geld een van de drie SaaS-beslissingen die je later niet terugdraait.

Wil je dat iemand anders deze keuzes vanaf de eerste migratie maakt: dat is precies het werk waarvoor wij een SaaS-product bouwen.