Data Types in SQL

When you create a table in SQL, you must tell the database:

👉 “What type of data will be stored in each column?”

You cannot store everything as text.

For example:

• Salary should be a number
• Name should be text
• Date of joining should be a date
• IsActive should be true or false

This is where Data Types come in.

What Are Data Types in SQL?

A Data Type defines:

• What kind of value a column can store
• How much space it will use
• What operations can be performed on it

Think of it like choosing the correct container:

• Water → Bottle
• Rice → Sack
• Documents → File

Similarly:

• Numbers → INT
• Text → VARCHAR
• Date → DATE

1. Numeric Data Types

Used for numbers.

Common Numeric Types (PostgreSQL & MySQL)

Example:

• salary → INT
• price → DECIMAL(10,2)
• quantity → INT

For money, always use:

DECIMAL(10,2)

DECIMAL(10,2)

Not FLOAT (to avoid rounding issues).

Also Read: What is SQL?

2. Text Data Types

Used for storing names, addresses, categories, etc.

Difference:

• VARCHAR(100) → Limits characters
• TEXT → Large text, no size limit

Example:

• first_name → VARCHAR(50)
• product_name → VARCHAR(100)
• description → TEXT

3. Date and Time Data Types

Used for storing dates and timestamps.

Important:

• PostgreSQL mainly uses TIMESTAMP
• MySQL commonly uses DATETIME

Example:

• joining_date → DATE
• sale_date → TIMESTAMP

4. Boolean Data Type

Stores True or False values.

Example:

• is_active → BOOLEAN

Values:

• TRUE / FALSE (Postgres)
• 1 / 0 (MySQL internally)

5. Auto Increment

This is where PostgreSQL and MySQL differ.

MySQL:

INT AUTO_INCREMENT

INT AUTO_INCREMENT

PostgreSQL:

SERIAL

SERIAL

Example:

MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id SERIAL PRIMARY KEY

id SERIAL PRIMARY KEY

Practical Example – FMCG Products Table

Now let’s create a realistic products table covering all major data types.

We will include:

• id
• product_name
• category
• price
• stock_quantity
• launch_date
• is_active
• description

PostgreSQL Version

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  price NUMERIC(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  launch_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  description TEXT
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  price NUMERIC(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  launch_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  description TEXT
);

MySQL Version

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  launch_date DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  description TEXT
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  launch_date DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  description TEXT
);

What You Learned

• Data Types define what kind of data a column can store
• Use INT for whole numbers
• Use DECIMAL/NUMERIC for money
• Use VARCHAR for text
• Use DATE or TIMESTAMP for dates
• PostgreSQL uses SERIAL or IDENTITY
• MySQL uses AUTO_INCREMENT

Choosing correct data types:

• Improves performance
• Prevents wrong data entry
• Makes data cleaning easier

Why This Is Important for Data Analysts

If salary is stored as TEXT:

• You cannot calculate SUM properly

If date is stored as VARCHAR:

• Sorting becomes incorrect

Wrong data type = messy reporting.

That’s why understanding data types is not optional.

It is foundational.

What’s Next?

👉 In the next article:

Create Database and Tables in SQL