PostgreSQL Data Types

Today, we are exploring the different data types we can use. By the end of the article, you should have some confidence in picking the right data types. If you're new to databases, think of data types as containers designed to hold specific kinds of information.

Why Data Types Matter

Choosing the correct data type is crucial in database design for several reasons. It ensures data integrity, preventing errors like storing "apple" in a DATE column. It optimizes performance; searching integers is faster than searching text. Specific types enable particular operations, such as date arithmetic with DATE types, which isn't possible with TEXT. Proper types also minimize storage waste, like avoiding BIGINT for small numbers.

With these benefits in mind, let's explore the most common SQL data types and their effective use in database design.

Numeric Types

Think of these as different-sized boxes for numbers.

INTEGER Types

• TINYINT: The shoebox of integers. Great for small numbers like age.
• SMALLINT: The backpack. Good for larger numbers, like the population of a town.
• INT or INTEGER: The standard moving box. Your go-to for most whole numbers.
• BIGINT: The shipping container. For when you need to count stars in the universe.

Example:

CREATE TABLE School_Stats (
    SchoolID INT,           -- Can handle up to about 2 billion schools
    StudentsEnrolled SMALLINT,  -- Assuming no school has over 32,767 students
    TotalGraduatesSinceFoundation BIGINT  -- For very large numbers over time
);

Decimal Types

When you need precision, like for money or scientific measurements.

• DECIMAL(p,s) or NUMERIC(p,s):
  • 'p' is the total number of digits
  • 's' is the digits after the decimal point

Example:

CREATE TABLE Product_Pricing (
    ProductID INT,
    Price DECIMAL(8,2),     -- Can store up to 999999.99
    WeightInKg DECIMAL(6,3)  -- Can store up to 999.999 kg
);

Floating-Point Types

For when you need to store really big or really small numbers, and exact precision isn't crucial.

• FLOAT: Like a rough estimate.
• DOUBLE PRECISION: A more precise estimate.

Example:

CREATE TABLE Scientific_Readings (
    ReadingID INT,
    ApproximateValue FLOAT,
    MorePreciseValue DOUBLE PRECISION
);

Text Types

For storing text.

CHAR(n)

Fixed-length strings. If you specify CHAR(10) and store "hello", it'll actually store "hello " (with 5 spaces).

VARCHAR(n)

Variable-length strings. VARCHAR(10) storing "hello" will just store "hello", no extra spaces.

TEXT

For storing large amounts of text, like essays or product descriptions.

Example:

CREATE TABLE User_Profiles (
    UserID INT,
    CountryCode CHAR(2),        -- Always 2 characters, like 'US' or 'CA'
    Username VARCHAR(50),       -- Usernames of varying length, up to 50 characters
    Biography TEXT              -- Can store long user bios
);

Date and Time Types

For when you need to work with dates and times.

DATE

Stores date in the format YYYY-MM-DD.

TIME

Stores time in the format HH:MM:SS.

TIMESTAMP

Stores both date and time.

Example:

CREATE TABLE Event_Schedule (
    EventID INT,
    EventDate DATE,             -- Stores just the date
    StartTime TIME,             -- Stores just the time
    CreatedAt TIMESTAMP         -- Stores both date and time
);

Boolean Type

For simple true/false data.

BOOLEAN or BOOL: Stores true/false values.

Example:

CREATE TABLE User_Settings (
    UserID INT,
    ReceiveEmails BOOLEAN,      -- True if user wants emails, False otherwise
    IsDarkModeEnabled BOOLEAN   -- True if user prefers dark mode, False for light mode
);

ENUM

A string object with a value chosen from a predefined list. Like a multiple-choice question where you can only pick one answer.

Example:

CREATE TABLE T_Shirt_Inventory (
    ShirtID INT,
    Size ENUM('Small', 'Medium', 'Large', 'XLarge')  -- Can only be one of these values
);

SERIAL

Auto-incrementing integer. Great for ID columns that you want to automatically number.

UUID

Universally Unique Identifier. Imagine a really, really long number that's guaranteed to be unique.

PostgreSQL-Specific Types

PostgreSQL, being the overachiever it is, offers some additional types:

JSON and JSONB

For storing JSON data. JSONB is a special PostgreSQL type that stores JSON in a custom binary format.

ARRAY

Allows you to store multiple values in a single column.

Example:

CREATE TABLE Advanced_Features (
    ID SERIAL PRIMARY KEY,      -- Auto-incrementing ID
    UniqueIdentifier UUID,      -- A unique identifier
    UserPreferences JSONB,      -- Stores JSON data
    Tags TEXT[]                 -- An array of text tags
);

Our next article will explore effective table design and normalization, building on this understanding of data types. Remember, choosing the correct data type is like picking the right tool for a job – it makes everything easier and more efficient in the long run.