Lecture 03: Data Types

DATA 351: Data Management with SQL

Lucas P. Cordova, Ph.D.
lpcordova@willamette.edu

Willamette University

January 26, 2026

Outline

Understanding PostgreSQL data types:

  • Numeric types
  • Text types
  • Date/time types
  • Other types

Understanding PostgreSQL data types

Why Data Types Matter

Consider this question: What is '10' + '5'?

  • In some languages: '105' (string concatenation)
  • In math: 15 (addition)

Warning

Data types tell PostgreSQL how to interpret and operate on your data.

Wrong data type = wrong results, wasted storage, or errors.

PostgreSQL Data Type Categories

Numeric Types: The Big Three

Type Description Example
INTEGER Whole numbers 42, -7, 1000000
NUMERIC(p,s) Exact decimals 75000.00, 3.14159
REAL Approximate decimals Scientific calculations

When to use each:

  • INTEGER: Counts, IDs, quantities
  • NUMERIC: Money, precise measurements
  • REAL: Scientific data where approximation is OK

NUMERIC(precision, scale) Explained

salary_usd NUMERIC(10,2)
  • Precision (10): Total digits allowed
  • Scale (2): Digits after decimal point
Value Valid for NUMERIC(10,2)?
75000.00 Yes (7 digits total)
123456789.99 No (11 digits total)
75000.001 Rounded to 75000.00

Quick Check: Our Employee Table

Look at how we defined numeric columns:

salary_usd         NUMERIC(10,2)    -- Up to 99,999,999.99
performance_rating NUMERIC(2,1)     -- 0.0 to 9.9
commission_rate    NUMERIC(3,2)     -- 0.00 to 9.99
years_experience   INTEGER          -- Whole years only

Question: Why not use INTEGER for salary? 🤔

Because we want cents! $75,000.00 not $75000

Text Types: Three Flavors

Type Description Use Case
CHAR(n) Fixed length, padded Codes like state abbreviations
VARCHAR(n) Variable length, max n Names, emails with limits
TEXT Unlimited length Long descriptions, notes

Note

In practice: TEXT and VARCHAR are equally fast in PostgreSQL.

I generally use TEXT unless I have a specific length constraint.

Date and Time Types

Type Stores Example
DATE Year, month, day '2026-01-15'
TIME Hour, minute, second '09:30:00'
TIMESTAMP Both date and time '2026-01-15 09:30:00'

Our table uses:

hire_date  DATE       -- Just the date they started
last_login TIMESTAMP  -- Date AND time of login

Boolean: True or False

The BOOLEAN type stores TRUE or FALSE.

PostgreSQL accepts multiple formats:

True Values False Values
TRUE, 't', 'yes', '1' FALSE, 'f', 'no', '0' 
SELECT full_name, is_manager 
FROM employees 
WHERE is_manager = TRUE;
full_name is_manager
Michael Scott t

NULL: The Absence of Data

NULL means “unknown” or “not applicable.”

Look at our data for commissions:

SELECT full_name, commission_rate, last_login
FROM employees
WHERE commission_rate IS NULL;
full_name commission_rate last_login
Michael Scott NULL 2026-01-15 09:12:00
Pam Beesly NULL NULL
Angela Martin NULL 2026-01-15 07:45:00

Note

Pam has no commission (not in sales) AND no last_login (maybe she uses paper).

NULL Gotchas

NULL is not equal to anything, not even itself!

-- This finds NOTHING:
SELECT * FROM employees WHERE commission_rate = NULL;


-- This works:
SELECT * FROM employees WHERE commission_rate IS NULL;

NULL Gotchas: Math with NULL

Math with NULL returns NULL

SELECT 100 + NULL;  -- Returns NULL
SELECT NULL * 5;    -- Returns NULL

Note

We will learn to handle this with COALESCE and NULLIF later.

Exercise: Data Type Detective

Look at this data and choose the best PostgreSQL data type:

  1. Social Security Number: '123-45-6789'
  2. Product price: $29.99
  3. Is item in stock: yes or no
  4. Number of items in warehouse: 1,547
  5. Customer review text: 'Great product! Would buy again...'

Take 2 minutes to decide, then we discuss.

Exercise: Data Type Answers

  1. SSN: CHAR(11) or VARCHAR(11) - It is text, not a number (leading zeros, dashes)

  2. Price: NUMERIC(10,2) - Money needs exact precision

  3. In stock: BOOLEAN - True/false value

  4. Warehouse count: INTEGER - Whole numbers only

  5. Review text: TEXT - Variable length, potentially long

Questions?

We covered:

  • Numeric, text, and date/time types
  • Boolean and NULL behavior
  • Choosing types that fit your data

What questions do you have?