Data Types and Constraints

Welcome back.

In the previous lesson, you learned the basic SQL actions:

• SELECT;
• INSERT;
• UPDATE;
• DELETE.

You learned how to read data.

Add data.

Change data.

Delete data.

Very powerful.

Also slightly dangerous.

Today we learn how to make tables safer.

Because a database should not accept everything.

If a column is for age, it should not accept:

banana

If an email must be unique, PostgreSQL should stop two users from sharing the same email.

If a name is required, PostgreSQL should not allow mysterious empty people to enter your table.

This is where data types and constraints help.

Data types define what kind of data a column can store.

Constraints define rules the data must follow.

Together, they make your database stronger.

Like a serious security guard.

But with semicolons.

What You Will Learn

In this lesson, you will learn:

• what data types are;
• why data types matter;
• how to use INTEGER;
• how to use VARCHAR;
• how to use TEXT;
• how to use BOOLEAN;
• how to use DATE;
• how to use NUMERIC;
• what constraints are;
• how to use NOT NULL;
• how to use UNIQUE;
• how to use DEFAULT;
• how to use CHECK;
• how to create safer tables;
• how PostgreSQL protects your data from chaos.

By the end of this lesson, you will understand how to design tables that do not accept nonsense.

This is important.

Because bad data is like glitter.

Once it enters the system, it appears everywhere.

Forever.

What Are Data Types?

A data type tells PostgreSQL what kind of value a column can store.

Example:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INTEGER
);

Here:

name VARCHAR(100)

means the name column stores text.

And:

age INTEGER

means the age column stores whole numbers.

So PostgreSQL understands:

name = text
age  = number

This is useful because PostgreSQL can reject wrong data.

If you try to put text into an INTEGER column, PostgreSQL will complain.

Good.

Databases should complain when nonsense arrives.

Silence is dangerous.

Especially in databases.

And kitchens.

Why Data Types Matter

Imagine this table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age TEXT
);

Technically, age is text here.

So PostgreSQL may allow:

INSERT INTO users (name, age)
VALUES ('Anna', 'twenty two');

Or even:

INSERT INTO users (name, age)
VALUES ('Marco', 'banana');

That is bad.

Age should be a number.

Better:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age INTEGER
);

Now PostgreSQL expects a number for age.

This protects your data.

Good structure prevents stupid data from entering the table.

PostgreSQL is basically saying:

No banana ages today.

Excellent.

Common PostgreSQL Data Types

PostgreSQL has many data types.

Today we will learn the most useful beginner ones:

• INTEGER;
• VARCHAR;
• TEXT;
• BOOLEAN;
• DATE;
• NUMERIC;
• SERIAL.

These are enough for many beginner projects.

Later, you can explore more advanced types.

PostgreSQL has many.

Because PostgreSQL looked at data and said:

Yes, I can organize all of this.

Very ambitious.

Very useful.

INTEGER

INTEGER stores whole numbers.

Example:

age INTEGER

Good values:

18
25
100

Bad values:

hello
25.5
banana

Example table:

CREATE TABLE players (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  score INTEGER
);

Insert data:

INSERT INTO players (name, score)
VALUES ('Anna', 100);

This works.

But this does not:

INSERT INTO players (name, score)
VALUES ('Marco', 'very good');

Because score expects a number.

PostgreSQL refuses.

As it should.

A database without standards is just a spreadsheet with ambition.

VARCHAR

VARCHAR stores text with a maximum length.

Example:

name VARCHAR(100)

This means:

The name can contain up to 100 characters.

Example:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  category VARCHAR(50)
);

This is good for text fields where you want a reasonable limit.

For example:

• names;
• titles;
• emails;
• categories;
• short labels.

If you try to insert text longer than the limit, PostgreSQL will reject it.

Good.

Sometimes limits are healthy.

Especially for database columns.

And pizza portions.

Maybe.

TEXT

TEXT stores long text.

Example:

description TEXT

Use TEXT when you do not need a strict character limit.

Good for:

• descriptions;
• article content;
• comments;
• notes;
• long messages.

Example:

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  content TEXT
);

Here:

title VARCHAR(150)

is limited.

But:

content TEXT

can be long.

This makes sense.

A title should not be a novel.

A content field can be.

PostgreSQL is practical.

Mostly.

BOOLEAN

BOOLEAN stores true or false values.

Example:

available BOOLEAN

Good values:

true
false

Example table:

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  completed BOOLEAN
);

Insert data:

INSERT INTO tasks (title, completed)
VALUES ('Learn PostgreSQL', false);

Later, you can update it:

UPDATE tasks
SET completed = true
WHERE title = 'Learn PostgreSQL';

BOOLEAN is perfect for yes/no values:

• active;
• completed;
• available;
• published;
• verified.

Do not store this as text:

yes
no
maybe
sort of

That way lies chaos.

Use BOOLEAN.

Let the database help you.

DATE

DATE stores calendar dates.

Example:

birth_date DATE

Insert a date like this:

'1992-10-23'

PostgreSQL likes the format:

YYYY-MM-DD

Example table:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  event_date DATE
);

Insert data:

INSERT INTO events (title, event_date)
VALUES ('PostgreSQL Practice', '2026-05-03');

Dates should be dates.

Not text.

Not random strings.

Not “next Friday maybe”.

PostgreSQL can work with dates properly only if you store them as dates.

Shocking.

But true.

NUMERIC

NUMERIC stores exact decimal numbers.

This is useful for money.

Example:

price NUMERIC(10, 2)

This means:

Up to 10 digits total, with 2 digits after the decimal point.

Example values:

49.99
100.00
1250.50

Example table:

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  price NUMERIC(10, 2)
);

Insert data:

INSERT INTO courses (title, price)
VALUES ('PostgreSQL Basics', 49.99);

For prices, NUMERIC is usually better than INTEGER.

Unless you store cents as integers.

That is also a valid approach.

But for beginners, NUMERIC(10, 2) is easy to understand.

Money deserves precision.

Especially when it leaves your account.

SERIAL

SERIAL creates an auto-incrementing integer.

Example:

id SERIAL PRIMARY KEY

This means PostgreSQL automatically generates IDs.

Example:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Insert data without id:

INSERT INTO students (name)
VALUES ('Anna');

PostgreSQL automatically gives Anna an ID.

Then another row gets the next ID.

And so on.

Very useful.

Do not manually manage IDs if PostgreSQL can do it for you.

Life is already complicated.

Let the database count.

What Are Constraints?

Constraints are rules for table data.

They tell PostgreSQL:

This value is required.
This value must be unique.
This value must follow a condition.
This value has a default.

Common constraints:

• PRIMARY KEY;
• NOT NULL;
• UNIQUE;
• DEFAULT;
• CHECK.

Constraints make tables safer.

They stop bad data before it enters the table.

Think of constraints as table rules.

Like:

No empty names.
No duplicate emails.
No negative prices.
No impossible ages.

Very reasonable.

PostgreSQL is strict.

But in this case, strict is good.

NOT NULL

NOT NULL means a column is required.

Example:

name VARCHAR(100) NOT NULL

This means every row must have a name.

Example table:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150)
);

This works:

INSERT INTO customers (name, email)
VALUES ('Anna', 'anna@example.com');

This does not work:

INSERT INTO customers (email)
VALUES ('no-name@example.com');

Because name is required.

Good.

No mysterious customers without names.

This is not a spy database.

Probably.

UNIQUE

UNIQUE means values in a column cannot repeat.

Very useful for emails.

Example:

email VARCHAR(150) UNIQUE

Table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE
);

Insert first user:

INSERT INTO users (name, email)
VALUES ('Anna', 'anna@example.com');

This works.

Try inserting another user with the same email:

INSERT INTO users (name, email)
VALUES ('Another Anna', 'anna@example.com');

PostgreSQL rejects it.

Because email must be unique.

This protects your data.

Without UNIQUE, duplicate emails can create login problems, account problems, and headaches.

Database headaches are not fun.

They wear heavy boots.

DEFAULT

DEFAULT gives a column a value when no value is provided.

Example:

active BOOLEAN DEFAULT true

Table:

CREATE TABLE subscribers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(150) UNIQUE NOT NULL,
  active BOOLEAN DEFAULT true
);

Insert without active:

INSERT INTO subscribers (email)
VALUES ('reader@example.com');

Now check:

SELECT * FROM subscribers;

The active column should be true.

PostgreSQL used the default value.

Defaults are useful when most rows should start with the same value.

For example:

• active DEFAULT true;
• completed DEFAULT false;
• published DEFAULT false;
• created_at DEFAULT CURRENT_DATE.

Good defaults reduce repetitive code.

And repetitive code is where tiny bugs build nests.

CHECK

CHECK creates a rule that values must follow.

Example:

price NUMERIC(10, 2) CHECK (price >= 0)

This means price cannot be negative.

Table:

CREATE TABLE store_products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0)
);

This works:

INSERT INTO store_products (name, price)
VALUES ('Mouse', 25.00);

This does not:

INSERT INTO store_products (name, price)
VALUES ('Magic Refund Product', -10.00);

PostgreSQL rejects it.

Good.

Negative prices may exist in accounting.

But not in this table.

The database protects the rule.

Very serious.

Very helpful.

CHECK with Age

Another example:

age INTEGER CHECK (age >= 0)

This means age cannot be negative.

Table:

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INTEGER CHECK (age >= 0)
);

This works:

INSERT INTO people (name, age)
VALUES ('Anna', 22);

This does not:

INSERT INTO people (name, age)
VALUES ('Time Traveler', -5);

PostgreSQL refuses.

Good.

If you need time travelers, create a separate table.

Maybe.

Combining Constraints

You can combine several constraints in one table.

Example:

CREATE TABLE app_users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  active BOOLEAN DEFAULT true,
  age INTEGER CHECK (age >= 0)
);

This table has many rules:

• id is the primary key;
• name is required;
• email is required;
• email must be unique;
• active defaults to true;
• age cannot be negative.

This is much safer than a table with no rules.

A table without constraints accepts too much.

It is too polite.

Databases should not be too polite.

They should protect data.

Like a serious bouncer at the club of information.

Create a Safer Products Table

Let us create a better products table.

First remove the old one if needed:

DROP TABLE IF EXISTS safe_products;

Now create:

CREATE TABLE safe_products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100) DEFAULT 'General',
  price NUMERIC(10, 2) CHECK (price >= 0),
  available BOOLEAN DEFAULT true
);

This table has better rules:

• name is required;
• category has a default;
• price cannot be negative;
• available defaults to true.

Insert data:

INSERT INTO safe_products (name, price)
VALUES ('Laptop', 900.00);

Check:

SELECT * FROM safe_products;

You should see:

category = General
available = true

PostgreSQL filled in defaults.

Nice.

The database is helping.

Finally.

Test the Rules

Try inserting a product without a name:

INSERT INTO safe_products (price)
VALUES (10.00);

PostgreSQL should reject it because name is NOT NULL.

Try inserting a negative price:

INSERT INTO safe_products (name, price)
VALUES ('Broken Product', -5.00);

PostgreSQL should reject it because of CHECK.

This is good.

The table is defending itself.

A table that defends itself is much safer than a table that accepts everything.

Like a door with a lock.

Not perfect.

But better than a curtain.

Inspect the Table

Run:

\d safe_products

You should see the columns, types, defaults, and constraints.

This is a useful command.

Use it often.

When you forget what your table looks like, ask PostgreSQL.

It remembers.

It always remembers.

Very database.

Slightly scary.

Common Mistakes

Using TEXT for Everything

Bad:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age TEXT,
  active TEXT
);

Better:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INTEGER,
  active BOOLEAN
);

Use the correct type.

Text is not a universal solution.

It is a useful tool.

Not a storage trash bag.

Forgetting NOT NULL

Bad:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150)
);

This allows users without names or emails.

Maybe not what you want.

Better:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL
);

If a value is required, say it.

PostgreSQL will enforce it.

Forgetting UNIQUE on Email

Bad:

email VARCHAR(150)

Better:

email VARCHAR(150) UNIQUE

If emails identify users, they should usually be unique.

Duplicate emails are like two people using the same passport.

Possible in a bad system.

Terrible idea.

Allowing Negative Prices

Bad:

price NUMERIC(10, 2)

Better:

price NUMERIC(10, 2) CHECK (price >= 0)

If negative prices do not make sense, block them.

Do not trust future code to always behave.

Future code is written by future humans.

Dangerous.

Practice

Create a table named employees.

It should have:

• id;
• name;
• email;
• salary;
• active;
• hired_at.

Use these rules:

• name is required;
• email is required and unique;
• salary cannot be negative;
• active defaults to true;
• hired_at is a date.

Example:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  salary NUMERIC(10, 2) CHECK (salary >= 0),
  active BOOLEAN DEFAULT true,
  hired_at DATE
);

Insert employees:

INSERT INTO employees (name, email, salary, hired_at)
VALUES ('Anna', 'anna@example.com', 2500.00, '2026-05-03');

INSERT INTO employees (name, email, salary, active, hired_at)
VALUES ('Marco', 'marco@example.com', 3000.00, false, '2026-05-04');

Read data:

SELECT * FROM employees;

Then test the constraints.

Try duplicate email.

Try negative salary.

Try missing name.

Let PostgreSQL stop you.

That is the point.

Mini Challenge

Create a table named courses.

It should have:

• id;
• title;
• description;
• price;
• published;
• created_at.

Rules:

• title is required;
• price cannot be negative;
• published defaults to false;
• created_at defaults to the current date.

Hint:

created_at DATE DEFAULT CURRENT_DATE

Insert at least three courses.

Then run:

SELECT * FROM courses;

Try inserting a course without a title.

Try inserting a course with a negative price.

PostgreSQL should reject bad data.

This is not PostgreSQL being annoying.

This is PostgreSQL being useful.

There is a difference.

Sometimes.

Summary

Today you learned:

• data types define what kind of data a column stores;
• INTEGER stores whole numbers;
• VARCHAR stores limited text;
• TEXT stores long text;
• BOOLEAN stores true or false;
• DATE stores dates;
• NUMERIC stores precise decimal values;
• SERIAL creates auto-incrementing IDs;
• constraints create rules for data;
• NOT NULL makes a column required;
• UNIQUE prevents duplicate values;
• DEFAULT provides a value automatically;
• CHECK validates values with a condition;
• good table design protects your database.

This is a big step.

You are no longer just creating tables.

You are creating safer tables.

A database should not accept nonsense.

It should protect structure.

It should reject bad data.

It should stop banana ages, duplicate emails, negative prices, and mysterious empty users.

PostgreSQL can do that.

If you tell it the rules.

Next Lesson

In the next lesson, we will learn filtering and sorting data.

We will go deeper into:

• WHERE;
• comparison operators;
• AND;
• OR;
• LIKE;
• ORDER BY;
• LIMIT.

Because once you have data, you need to find the right data.

Not all data.

The right data.

That is where queries become interesting.

And slightly dangerous.