Get startedGet started for free

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

View Course

Exercise instructions

  • Complete the definition the loan table including an approval_date to represent the date when a loan is initially approved.

  • Set the precision for the decimal-valued gross_approval column 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_status to be represented by values of true and false.

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
);
Edit and Run Code