Altering Tables: Adding, Modifying, and Dropping Columns

While relational databases are traditionally known for strict, flat tabular structures, modern applications often require handling semi-structured or multi-valued data. PostgreSQL addresses this by allowing columns to store complex data types such as JSONB (binary JSON) and Arrays.

This chapter covers how to define, modify, and query these advanced column types, transitioning from rigid schemas to flexible, hybrid data models.

1. Introduction to Complex Column Types: JSONB and Arrays

When designing database schemas, we often encounter attributes that do not fit cleanly into a single scalar column (like VARCHAR or INT).

Historically, developers had two choices:

1. Normalize the database: Create auxiliary tables and establish foreign key relationships (which increases query complexity and requires multiple joins).
2. Serialize to text: Store JSON or comma-separated values as a raw string (which prevents the database from indexing or querying internal fields).

PostgreSQL solves this dilemma by providing native support for JSONB and Arrays directly within table columns.

2. Mental Model: Relational vs. Document-Based Storage

To understand when and why to use these types, consider the following real-world analogy:

Real-Life Analogy: The Student Folder

• Relational Columns (Standard): Think of pre-printed fields on the outside of a physical student folder—such as Student ID and Enrollment Date. Every folder has exactly these fields, and they always contain a single value.
• Array Columns: Think of a list of "Extracurricular Activities" written on a single line. Instead of creating 5 separate boxes for 5 possible activities, you write them down as a list: [Soccer, Chess, Drama].
• JSONB Columns: Think of a plastic sleeve inside the folder where you can slip in custom sticky notes. One student's sleeve might contain a note about their computer specs: {"OS": "macOS", "RAM": "16GB"}. Another student's sleeve might contain details about their off-campus internship: {"company": "Stripe", "role": "Backend Intern", "stipend": 5000}. The database doesn't force a rigid structure on this sleeve, yet you can still reach in and read specific lines on those sticky notes.

3. Working with JSONB: Structure and Insertion

The JSONB data type stores JSON data in a decomposed binary format. While this adds a minor overhead during insertion due to parsing, it makes lookups and query executions significantly faster because the database does not need to re-parse the JSON string every time it filters a row.

Defining a Table with JSONB and Array Columns

Below is a realistic schema representing a course enrollment system that utilizes both JSONB (course_meta) and TEXT[] (skills) columns:

CREATE TABLE course_enrollment (
    id SERIAL PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    skills TEXT[] NOT NULL, -- Array of text
    course_meta JSONB NOT NULL -- Binary JSON object
);

Inserting Data

When inserting data into these columns:

• Arrays are formatted using the ARRAY[...] constructor or string literal syntax '{val1, val2}'.
• JSONB values are passed as valid JSON strings, which PostgreSQL automatically validates and converts to binary format.

INSERT INTO course_enrollment (course_name, skills, course_meta)
VALUES 
(
    'Full Stack Web Development', 
    ARRAY['React', 'Next.js', 'Node.js'], 
    '{"duration": 30, "instructor": "Hitesh", "platform": "Expo"}'
),
(
    'Advanced Backend Engineering', 
    ARRAY['Go', 'Docker', 'PostgreSQL'], 
    '{"duration": 45, "instructor": "Choudhary"}'
),
(
    'Mobile App Development', 
    ARRAY['React Native', 'Expo'], 
    '{"duration": 20, "instructor": "Hitesh", "platform": "Expo"}'
);

4. Querying JSONB Fields: The Arrow Operators

Querying nested JSONB properties requires specialized operators. PostgreSQL provides two primary operators to extract data from a JSONB column:

The "Missing Property" Pitfall (Debugging Workflow)

During development, querying JSON fields can return unexpected NULL values if there is a mismatch between the queried key and the actual key stored in the database.

The Naive/Incorrect Query:

A developer attempts to query the mentor property from the course_meta column:

SELECT course_meta -> 'mentor' AS mentor_name 
FROM course_enrollment;

Output:

Why did this happen?
The database did not throw an error; it simply returned NULL. This is because the key 'mentor' does not exist inside the course_meta JSON objects. The actual key stored in the database is 'instructor'.

The Corrected Query:

To extract the actual instructor or duration, use the correct keys:

SELECT 
    course_name,
    course_meta ->> 'instructor' AS instructor,
    course_meta ->> 'duration' AS duration_weeks
FROM course_enrollment;

Output:

Querying Sparse/Optional JSONB Properties

One of the main benefits of JSONB is its schema-less nature. Some rows may contain certain keys that other rows lack.

For example, querying the 'platform' key:

SELECT course_name, course_meta ->> 'platform' AS platform
FROM course_enrollment;

Output:

Note: PostgreSQL handles missing keys gracefully by returning NULL for rows where the key does not exist (such as the "Advanced Backend Engineering" course), rather than crashing the query.

5. Working with Array Data Types (TEXT[])

Arrays allow you to store multiple ordered values of the same data type in a single column. This is highly efficient for tags, categories, or list-based attributes.

Updating Array Columns

To update or replace an array value, you can assign a new array directly to the column:

UPDATE course_enrollment
SET skills = ARRAY['React', 'Next.js', 'TypeScript', 'Tailwind']
WHERE course_name = 'Full Stack Web Development';

6. Filtering Arrays: The ANY Operator

When you want to filter rows based on whether an array column contains a specific element, you cannot use a simple = operator because the column contains a list, not a scalar value. Instead, you use the ANY operator.

Syntax Flow of the ANY Operator

Example Query:

To find all courses that teach 'React':

SELECT course_name, skills 
FROM course_enrollment 
WHERE 'React' = ANY(skills);

Output:

7. Advanced Array Filtering: The Containment Operator (@>)

While the ANY operator is readable and clean, PostgreSQL also offers a powerful set of native array operators. The most common of these is the Containment Operator (@>).

The @> operator checks if the array on the left side contains all elements of the array on the right side.

Syntax:

left_array @> right_array

Example Query:

To find courses that contain both 'React' and 'Next.js' in their skills array:

SELECT course_name, skills 
FROM course_enrollment 
WHERE skills @> ARRAY['React', 'Next.js']::text[];

Comparison of Array Filtering Methods

8. Summary and Key Takeaways

• JSONB vs. JSON: JSONB stores data in a parsed binary format. It is slightly slower to write but significantly faster to query and index compared to plain text JSON.
• Arrow Operators: Use -> to extract values as JSONB objects, and ->> to extract values as raw, unquoted text.
• Graceful NULL Handling: Querying a non-existent key in a JSONB column returns NULL instead of throwing an error, making it ideal for sparse, dynamic schemas.
• Array Storage: Use TEXT[] or other type arrays to store ordered lists of scalar values in a single column.
• The ANY Operator: Use value = ANY(array_column) to filter rows where at least one element in the array matches the target value.
• Containment Operator (@>): Use the @> operator to check if an array column contains a specific set of elements, which is highly performant when indexed with GIN.