Choosing data types representations
In this chapter, you have explored many of the data types available for use in PostgreSQL databases including text, numeric, temporal, and boolean data types. Continuing with our SBA data example, let's gain some experience determining which data types to use to best represent the data that you are interested in storing. For example, if you wanted to understand the monthly payment rates of a borrower, it would be helpful to have the loan amount and interest rate represented as numeric values to aid in such a calculation.
Your manager has asked you to create a new loan table that requires specifying the correct data type and properties to use for the table columns.
This exercise is part of the course
Creating PostgreSQL Databases
Exercise instructions
Complete the definition the loan table including an
approval_dateto represent the date when a loan is initially approved.Set the precision for the decimal-valued
gross_approvalcolumn to allow loan amounts up to $5,000,000.Provide a data type to best represent the length (in months) for loan repayment using
term_in_months.Define the data type for the column
revolver_statusto be represented by values oftrueandfalse.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create the loan table
___ ___ ___ (
borrower_id INTEGER REFERENCES borrower(id),
bank_id INTEGER REFERENCES bank(id),
-- 'approval_date': the loan approval date
approval_date ___ NOT NULL DEFAULT CURRENT_DATE,
-- 'gross_approval': amounts up to $5,000,000.00
gross_approval DECIMAL(___, 2) NOT NULL,
-- 'term_in_months': total # of months for repayment
term_in_months ___ NOT NULL,
-- 'revolver_status': TRUE for revolving line of credit
revolver_status ___ NOT NULL DEFAULT FALSE,
initial_interest_rate DECIMAL(4, 2) NOT NULL
);