Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

239
Views
How to use a custom function as column default in a PostgreSQL table

I created a table named Loan that contains the following columns:

loanID SERIAL, 
annualInterestRate INT, 
numberOfYears INT, 
loanAmount NUMERIC, 
monthlyPayment NUMERIC

The calculation of the monthlyPayment depends on numberOfYears, loanAmount, and the annualInterestRate as per the following formula:

monthlyPayment = (loanAmount * monthlyInterestRate) /
                (1 - (1/Math.pow(1 + monthlyInterestRate , numberOfYears * 12) ));

I made a function named get_monthly_payment() that returns the monthlyPayment with no problem. For each row, I want to make the return of this function the default of the column monthlyPayment. How can I achieve this?

over 4 years ago · Santiago Trujillo
3 answers
Answer question

0

If monthlyPayment is fixed as per your definition, hence completely functionally dependent, then consider not persisting the value at all. Keep using your cheap (!) function instead. Much cleaner and cheaper overall. Like:

SELECT *, f_monthly_payment(l) AS monthly_payment
FROM loan l;

Assuming the function is defined as f_monthly_payment(loan) (taking the row type of the table as argument). Else adapt accordingly.

Postgres 12 or later has STORED generated columns, but those only pay for expensive calculations. Persisted columns occupy space and slow down all operations on the table.

See:

  • Computed / calculated / virtual / derived columns in PostgreSQL

If you want to allow manual changes, a column default is the way to go (like you actually asked). But you cannot use your function because, quoting the manual:

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed).

The remaining solution for this is a trigger BEFORE INSERT on the table like:

CREATE OR REPLACE FUNCTION trg_loan_default_rate()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.monthlyPayment := (NEW.loanAmount * monthlyInterestRate())
                    / (1 - (1/power(1 + monthlyInterestRate(), NEW.numberOfYears * 12)));

   RETURN NEW;
END
$func$;

CREATE TRIGGER loan_default_rate
BEFORE INSERT ON loan
FOR EACH ROW EXECUTE FUNCTION trg_loan_default_rate();

Assuming monthlyInterestRate() is a custom function.
And I replaced Math.pow with the built-in Postgres function power().

NEW is a special record variable in trigger functions, referring to the newly inserted row. See:

  • FOR EACH STATEMENT trigger example

EXECUTE FUNCTION requires Postgres 11. See:

  • Trigger function does not exist, but I am pretty sure it does

Related:

  • PostgreSQL - set a default cell value according to another cell value

Aside: consider legal, lower-case, unquoted identifiers in Postgres. See:

  • Are PostgreSQL column names case-sensitive?
over 4 years ago · Santiago Trujillo Report

0

In Postgres 12+ you have generated columns:

A generated column is a special column that is always computed from other columns.

"A generated column is a special column that is always computed from other columns. ..."

So:

monthlyPayment numeric GENERATED ALWAYS AS(loanAmount * monthlyInterestRate) /
                (1 - (1/Math.pow(1 + monthlyInterestRate , numberOfYears * 12) ))  STORED

Before version 12 you have to use CREATE TRIGGER to add a trigger to the table that calls a function that contains the above equation and sets the column value.

over 4 years ago · Santiago Trujillo Report

0

Monthly Interest rate may not be a column on your table, but the Annual rate is. But Annual to Monthly is a simple calculation. The following can be used for both v12 generated columns and prior versions using a trigger. Additionally it provides what could be a generally usefully Postgres function for monthly payment calculations. See fiddle for each.

create or replace 
function monthly_loan_payment 
       ( amount  numeric
       , apr     numeric
       , term    numeric
       )
  returns numeric
  language sql 
  immutable strict  
/*  Given a loan amount, the Annual Percent (Interest) Rate and term (in years) 
 *  compute the monthly payment to service the loan. 
 *  Note. Monthly payment calculates correctly, but due to the exact terms of loan
 *        and date of payment receipt adjustments for end of loan payment may be 
 *        required.  
 */ 
as $$   
  with monthly (mrate) as (values ( (apr/100.00) / 12.00 ) )
  select round((amount * mrate)  /(1.0 - (1.0/ ((1.0 +mrate)^( term * 12.00)) ))::numeric,2) 
     from monthly;
$$; 
over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!