What Are Data Types in PostgreSQL: Explained with Examples

In PostgreSQL, data types define the kind of data a column can hold, such as numbers, text, or dates. They help the database understand how to store, process, and validate the data efficiently.

How It Works

Think of data types in PostgreSQL like different containers for storing things. Just as you wouldn't put soup in a shoe box, you use the right data type to store the right kind of information. For example, numbers go into numeric types, words into text types, and dates into date/time types.

When you create a table, you assign a data type to each column. This tells PostgreSQL how to handle the data, such as how much space to reserve and what operations are allowed. Using the correct data type helps keep your data accurate and your queries fast.

Example

This example shows how to create a table with different data types for each column.

sql

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

INSERT INTO employees (name, salary, hire_date, is_active) VALUES
('Alice', 55000.00, '2022-01-15', true),
('Bob', 48000.50, '2023-03-01', false);

SELECT * FROM employees;

Output

 id | name  | salary  | hire_date  | is_active 
----+-------+---------+------------+-----------
  1 | Alice | 55000.00| 2022-01-15 | t         
  2 | Bob   | 48000.50| 2023-03-01 | f         

When to Use

Use data types in PostgreSQL whenever you create tables to store data. Choosing the right data type ensures your data is stored efficiently and correctly. For example, use BOOLEAN for true/false values, DATE for calendar dates, and NUMERIC for precise decimal numbers like money.

In real life, if you are building a payroll system, you would use numeric types for salaries, date types for hire dates, and text types for employee names. This helps the system validate data and perform calculations accurately.

Key Points

• Data types define what kind of data a column can hold.
• Choosing the right data type improves data accuracy and performance.
• PostgreSQL supports many types like INTEGER, TEXT, BOOLEAN, DATE, and NUMERIC.
• Use data types to help the database validate and process data correctly.

Key Takeaways

Data types tell PostgreSQL how to store and handle data in each column.

Use the correct data type to keep data accurate and queries efficient.

PostgreSQL offers many built-in data types for numbers, text, dates, and more.

Defining data types helps the database validate data and optimize storage.

Choosing data types carefully is essential for building reliable databases.