PostgreSQL query for currency conversion rates

Question

Let's consider the database table for currency conversion looks as below:

fromCurrency	toCurrency	effective date	conversion rate
USD	INR	1-Mar-2024	80
USD	INR	1-Jan-2024	85
USD	GBP	1-Oct-2023	.80
USD	GBP	1-Mar-2024	.85
USD	AUD	1-Mar-2023	1.55
USD	AUD	1-Mar-2024	1.60

If database is queried on 1-Feb-2024, to get the conversion rate where fromCurrency='USD', it should return

fromCurrency	toCurrency	effective date	conversion rate
USD	INR	1-Jan-2024	85
USD	GBP	1-Oct-2023	.80
USD	AUD	1-Mar-2023	1.55

The same query on 15-April-2024 should return

fromCurrency	toCurrency	effective date	conversion rate
USD	INR	1-Mar-2024	80
USD	GBP	1-Mar-2024	.85
USD	AUD	1-Mar-2024	1.60

Basically for all possible combinations of (fromCurrency, toCurrency) there many many records in the database and on a particular date when the database is queried only one of those many records is applicable based on the effective date. I am trying to figure out the best possible postgres query to get the records.

Answer 1

Both in jOOQ and native PostgreSQL, you can use DISTINCT ON, which is a clause where you can request only the first record per DISTINCT ON expression, given the ORDER BY clause:

With SQL:

SELECT DISTINCT ON (fromCurrency, toCurrency) 
  fromCurrency, toCurrency, effectiveDate, conversionRate
FROM conversions
WHERE effectiveDate <= :currentDate
ORDER BY fromCurrency, toCurrency, effectiveDate DESC

With jOOQ (since you tagged the question with jOOQ):

var c = CONVERSIONS.as("c");

ctx.select(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE, c.CONVERSION_RATE)
   .distinctOn(c.FROM_CURRENCY, c.TO_CURRENCY)
   .from(c);
   .where(c.EFFECTIVE_DATE.le(currentDate))
   .orderBy(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE.desc())
   .fetch();

See this db-fiddle