By the end of this lesson, you will be able to:

  • Identify and describe common SQL data types: TEXT, NUMERIC, BOOLEAN, and DATE/TIME

  • Choose appropriate data types for different kinds of data

  • Understand how these types affect storage, querying, and constraints

  • Write SQL statements using the correct data types

What Are Data Types?

In SQL, data types define the kind of values a column can store — such as text, numbers, dates, or logical values (true/false).
Choosing the right data type ensures data accuracy, efficient storage, and optimal query performance.

When creating a table, every column must have a defined data type:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  salary NUMERIC(10, 2),
  is_active BOOLEAN,
  hire_date DATE
);

TEXT Data Type

Used to store textual or alphanumeric data such as names, addresses, or descriptions.

 
TypeDescriptionExample
CHAR(n)Fixed-length string (pads with spaces if shorter)'USA' (CHAR(3))
VARCHAR(n)Variable-length string (max length = n)'John Doe'
TEXTStores long text with no fixed limit'Data Engineer and course creator'

Tip

- Use VARCHAR for most cases (flexible + efficient).
- Use TEXT for large content (e.g., descriptions).
- Avoid overly long limits unless necessary.

Example:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  full_name VARCHAR(100),
  bio TEXT
);

Numeric Data Types

Used for numbers — integers, decimals, and floating-point values.

TypeDescriptionExample
INT / INTEGERWhole numbers42, -10
SMALLINTSmaller range integers100
BIGINTVery large integers9876543210
NUMERIC(p, s) / DECIMAL(p, s)Exact decimal values (precision, scale)1234.56
REAL / FLOATApproximate floating-point numbers3.14159

Tip

- Use INT for IDs and counters
- Use NUMERIC for money or prices (exact precision)
- Avoid FLOAT for financial values (may introduce rounding errors)

Example:

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  price NUMERIC(8,2),
  quantity INT
);

Boolean Data Type

Used to store logical values: TRUE or FALSE.

 
TypePossible ValuesExample
BOOLEANTRUE, FALSE, NULLis_active BOOLEAN

Tip

Booleans can also be inserted as 't', 'f', 1, or 0 (PostgreSQL interprets them automatically).

Example:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  is_full_time BOOLEAN DEFAULT TRUE
);

Date & Time Data Types

Used to store temporal information (dates, timestamps, durations).

 
TypeDescriptionExample
DATEStores only the date (YYYY-MM-DD)'2025-10-12'
TIMEStores only time (HH:MM:SS)'14:30:00'
TIMESTAMPStores both date and time'2025-10-12 14:30:00'
TIMESTAMPTZTimestamp with timezone'2025-10-12 14:30:00+02'

Tip

Booleans can also be inserted as 't', 'f', 1, or 0 (PostgreSQL interprets them automatically).

Example:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  shipped_at TIMESTAMP
);

Mixing Data Types — Type Casting

Sometimes, you’ll need to convert between data types to make operations compatible.

Example:

SELECT '100'::INT + 20;        -- converts text to integer
SELECT price::TEXT;            -- converts numeric to text

Warning

Be careful: incorrect conversions can fail or produce unexpected results.

PreviousNext