---
title: "Exchange Rates API Data in a Database: Schema, Indexes and As-Of Queries"
description: "Store exchange rates API data properly: a Postgres schema with NUMERIC rates, keys, as-of lookups, ON CONFLICT upserts and daily loading."
canonical: https://allratestoday.com/blog/store-exchange-rates-database-schema/
source: AllRatesToday (https://allratestoday.com/) — Markdown mirror of the page above; index of all pages at https://allratestoday.com/llms.txt
generated: 2026-11-11T15:23:39.157Z
---

Sooner or later, every app that uses an exchange rates API needs its own copy of the rates. Invoices must be reproducible next year. Reports must use the rate of the transaction date, not today's. And you do not want a page view to depend on a network call. This guide gives a schema that holds up: one table, the right keys, an as-of lookup, idempotent loading and a retention plan. The SQL is for PostgreSQL, with notes for MySQL and SQLite at the end.

## The table

```
CREATE TABLE fx_rate (
  base        CHAR(3)        NOT NULL,             -- ISO 4217, e.g. 'USD'
  quote       CHAR(3)        NOT NULL,             -- 1 base = rate quote
  rate        NUMERIC(20,10) NOT NULL CHECK (rate > 0),
  rate_type   TEXT           NOT NULL CHECK (rate_type IN ('mid', 'official')),
  source      TEXT           NOT NULL,             -- 'allratestoday', 'ecb', 'rbi', ...
  rate_date   DATE           NOT NULL,             -- the date the rate applies to
  rate_time   TIMESTAMPTZ,                         -- the provider's own timestamp, if any
  fetched_at  TIMESTAMPTZ    NOT NULL DEFAULT now(),
  PRIMARY KEY (base, quote, rate_type, source, rate_date)
);

CREATE INDEX fx_rate_date_idx ON fx_rate (rate_date);
```

Why each column is there:

- **base and quote** fix the direction. A row `USD, EUR, 0.9215` means 1 USD = 0.9215 EUR. Write that rule in a comment, because direction bugs are the most expensive kind.
- **rate as `NUMERIC(20,10)`**, never `FLOAT` or `DOUBLE`. Floating point turns 0.9215 into 0.92149999... and those errors show up as one-cent differences in totals. Ten decimal places covers small-unit currencies against strong ones, and ten integer digits covers rates such as USD/IRR or USD/VND.
- **rate_type** separates a live mid-market rate from an official central bank rate. They are different numbers with different purposes, and a report must never mix them by accident.
- **source** names who produced the rate. For official rates it is the institution, such as `ecb`. This is the column an auditor asks about.
- **rate_date** is the business date the rate applies to. **rate_time** is the provider's timestamp. **fetched_at** is when your system got it. Keep all three; they answer different questions.

## Primary key and indexes

The primary key `(base, quote, rate_type, source, rate_date)` says: one rate per pair, per type, per source, per day. That is the right grain for invoices, accounting and reports.

The column order is deliberate. Lookups filter on pair, type and source, then range on date, so the primary key index serves the as-of query directly. The extra index on `rate_date` is for retention jobs and for "all rates on a date" exports.

If you also keep intraday snapshots, put them in a separate table keyed on `rate_time` instead of `rate_date`. Mixing grains in one table makes every query ask "which row of the day?".

## As-of lookups: the latest rate on or before a date

Rates do not exist for every date. Official rates skip weekends and holidays, and your loader might miss a day. The standard question is "the latest rate on or before date D".

For several currencies at once, `DISTINCT ON` is the idiomatic Postgres answer:

```
SELECT DISTINCT ON (quote)
       quote, rate, rate_date
FROM fx_rate
WHERE base = 'USD'
  AND quote IN ('EUR', 'GBP', 'JPY')
  AND rate_type = 'mid'
  AND source = 'allratestoday'
  AND rate_date <= DATE '2026-11-08'
ORDER BY quote, rate_date DESC;
```

To attach a rate to each row of another table, use `LATERAL`. This converts invoices into USD with the rate on or before each invoice date, and refuses rates more than seven days older than the invoice:

```
SELECT i.id,
       i.amount,
       i.currency,
       r.rate,
       r.rate_date,
       CASE WHEN i.currency = 'USD' THEN i.amount
            ELSE round(i.amount / r.rate, 2) END AS amount_usd
FROM invoice i
LEFT JOIN LATERAL (
  SELECT rate, rate_date
  FROM fx_rate
  WHERE base = 'USD'
    AND quote = i.currency
    AND rate_type = 'mid'
    AND source = 'allratestoday'
    AND rate_date <= i.invoice_date
    AND rate_date >= i.invoice_date - 7
  ORDER BY rate_date DESC
  LIMIT 1
) r ON true;
```

Each lateral subquery is a single index probe on the primary key. `LEFT JOIN ... ON true` keeps invoices with no rate, so you see a `NULL` instead of silently losing rows. Since `rate` is EUR per USD, a EUR amount divided by the rate gives USD.

## Why not store inverses

It is tempting to store both `USD/EUR` and `EUR/USD`. Do not:

- **They can disagree.** If one is loaded and the other is not, or both are rounded, `a * b` stops being exactly 1, and two reports give two answers.
- **It doubles the rows** and the load work for no new information.
- **It hides direction bugs.** With one direction stored, a query that uses the wrong direction returns nothing, which you notice. With both stored, it returns a plausible wrong number.

Store one direction per source and compute the inverse when you need it: `1 / rate`. With `NUMERIC`, the inverse is exact to the precision you ask for.

## Triangulation through USD or EUR

You do not need rows for every pair either. With 160 currencies there are over 25,000 pairs. Store each currency against one base and derive the rest. EUR/GBP is USD/GBP divided by USD/EUR:

```
SELECT a.rate_date,
       round(b.rate / a.rate, 10) AS eur_gbp
FROM fx_rate a
JOIN fx_rate b USING (base, rate_type, source, rate_date)
WHERE a.base = 'USD' AND a.quote = 'EUR'
  AND b.quote = 'GBP'
  AND a.rate_type = 'mid' AND a.source = 'allratestoday'
ORDER BY a.rate_date DESC
LIMIT 30;
```

The important rule: **triangulate only within one source and one date**. Official rates come in each institution's own base. The ECB quotes against EUR, a central bank in India quotes against INR. Store them as given, with their own base and `source = 'ecb'` and so on, and never combine an ECB rate with another bank's rate into a cross rate. The result would belong to no one. The [base currency conversion guide](https://allratestoday.com/blog/how-to-handle-base-currency-conversion/) goes deeper on cross rates.

## Idempotent upserts with ON CONFLICT

Loaders get re-run: a cron fires twice, a job retries after a timeout, someone backfills a week by hand. The insert must be safe to repeat:

```
INSERT INTO fx_rate (base, quote, rate, rate_type, source, rate_date, rate_time)
VALUES ($1, $2, $3, 'mid', 'allratestoday', $4, $5)
ON CONFLICT (base, quote, rate_type, source, rate_date)
DO UPDATE SET rate       = EXCLUDED.rate,
              rate_time  = EXCLUDED.rate_time,
              fetched_at = now()
WHERE fx_rate.rate IS DISTINCT FROM EXCLUDED.rate;
```

Running it twice leaves one row. The `WHERE` clause skips writes when nothing changed, which keeps write volume and table bloat down. For official rates, consider `DO NOTHING` plus a log entry when a value differs, so a correction by the institution is visible rather than silently overwritten.

## Loading daily from the exchange rates API

One request fetches every currency you need. The AllRatesToday rates endpoint takes a comma-separated target list and returns a JSON array; a multi-target call costs one request.

```
# load_rates.py   (pip install requests "psycopg[binary]")
import json
import os
from datetime import datetime
from decimal import Decimal

import psycopg
import requests

TARGETS = ["EUR", "GBP", "JPY", "CAD", "AUD", "CHF", "INR", "CNY"]

UPSERT = """
INSERT INTO fx_rate (base, quote, rate, rate_type, source, rate_date, rate_time)
VALUES (%s, %s, %s, 'mid', 'allratestoday', %s, %s)
ON CONFLICT (base, quote, rate_type, source, rate_date)
DO UPDATE SET rate = EXCLUDED.rate, rate_time = EXCLUDED.rate_time, fetched_at = now()
WHERE fx_rate.rate IS DISTINCT FROM EXCLUDED.rate
"""

resp = requests.get(
    "https://allratestoday.com/api/v1/rates",
    params={"source": "USD", "target": ",".join(TARGETS)},
    headers={"Authorization": f"Bearer {os.environ['ALLRATES_API_KEY']}"},
    timeout=15,
)
resp.raise_for_status()
rows = json.loads(resp.text, parse_float=Decimal)   # keep exact decimals

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    for r in rows:
        ts = datetime.fromisoformat(r["time"].replace("Z", "+00:00"))
        conn.execute(UPSERT, (r["source"], r["target"], r["rate"], ts.date(), ts))
```

`parse_float=Decimal` keeps the rate exact from JSON to `NUMERIC`; going through a Python float would reintroduce rounding. Schedule the loader at a fixed time each day, for example just after your business close. If it runs twice the same day, the upsert keeps the later snapshot. Daily loading is about 30 requests a month, which fits any paid plan; commercial use starts on the Small plan, see [pricing](https://allratestoday.com/pricing/).

For backfills, `/api/historical-rates?source=USD&target=EUR&period=1y` returns weekly points and costs 12 requests per pair. For daily official rates over a date range, the central bank history endpoint, such as `/api/v1/central-bank/ecb/history?source=USD&target=EUR&from=2026-01-01&to=2026-03-31`, returns the institution's own dates and is billed per month of range. See the [central bank rates API](https://allratestoday.com/central-bank-rates-api/).

## Retention

Daily rows are tiny. 160 currencies for one source is about 58,000 rows a year, a few megabytes with indexes. Keep daily rates forever; old invoices and audits need them.

Intraday snapshots are different. One snapshot a minute for 160 currencies is about 230,000 rows a day. Partition that table by month and drop partitions you no longer need, keeping only the daily row in `fx_rate` long term.

## MySQL and SQLite notes

| Topic | PostgreSQL | MySQL 8 | SQLite |
| --- | --- | --- | --- |
| Rate column | `NUMERIC(20,10)` | `DECIMAL(20,10)` | `TEXT`, parse in the app |
| Upsert | `ON CONFLICT ... DO UPDATE` | `ON DUPLICATE KEY UPDATE` | `ON CONFLICT ... DO UPDATE` (3.24+) |
| As-of, many rows | `DISTINCT ON` | `ROW_NUMBER() OVER (PARTITION BY quote ORDER BY rate_date DESC)` | `ROW_NUMBER()` (3.25+) |
| As-of per row | `LATERAL` | `LATERAL` (8.0.14+) or correlated subquery | correlated subquery |
| Timestamps | `TIMESTAMPTZ` | `DATETIME` in UTC | ISO 8601 `TEXT` in UTC |

SQLite deserves a warning: a column declared `NUMERIC` stores 0.9215 as a floating-point value. Store rates as text and convert with a decimal type in your application.

If you are still choosing where the data comes from, the [free currency API comparison](https://allratestoday.com/blog/best-free-currency-exchange-api-2026/) lists update frequencies, history depth and limits.

## FAQ

### What data type should I use for exchange rates in SQL?

Use NUMERIC or DECIMAL, for example NUMERIC(20,10). Never use FLOAT or DOUBLE, because binary floating point cannot represent most decimal rates exactly and the errors show up in totals.

### How do I get the exchange rate for a date with no rate?

Use an as-of lookup: the latest rate on or before the date. In Postgres, use DISTINCT ON for many currencies or a LATERAL subquery with ORDER BY rate_date DESC LIMIT 1 per row, plus a maximum age.

### Should I store both USD/EUR and EUR/USD?

No. Store one direction per source and compute the inverse as 1 divided by the rate. Storing both lets them drift apart.

### How do I avoid duplicate rows when the loader runs twice?

Make the primary key the natural key (base, quote, rate type, source, rate date) and insert with ON CONFLICT DO UPDATE. Running the load again then updates or skips the row instead of adding one.

### Can I mix official and mid-market rates in one table?

Yes, as long as rate_type and source are part of the key and every query filters on them. Never build a cross rate from two different sources.

## Load rates into your database

Mid-market rates for 160+ currencies in one request, plus official rates from [123 central banks and 6 tax authorities](https://allratestoday.com/central-bank-rates-api/institutions/) with their own rate dates.

 [See plans](https://allratestoday.com/pricing/)

## Related Articles

- [How to handle base currency conversion](https://allratestoday.com/blog/how-to-handle-base-currency-conversion/)
- [Best free historical exchange rate API](https://allratestoday.com/blog/best-free-historical-exchange-rate-api-2026/)
- [How official rates are collected and served](https://allratestoday.com/official-rates-methodology/)
- [Central bank exchange rates API](https://allratestoday.com/central-bank-rates-api/)
- [Exchange rates in Excel](https://allratestoday.com/blog/excel-exchange-rates/)
- [Build an exchange rate alert](https://allratestoday.com/blog/currency-rate-alert-api/)