PostgreSQL Deep Dive Part 7 — Managing Time with Intervals and Generated Columns

PostgreSQL offers powerful features for handling time durations and automated column calculations. In this deep dive, we'll explore two critical features: Intervals for managing time durations and Generated Columns for automated calculations.

Understanding Intervals in PostgreSQL

Intervals in PostgreSQL represent durations or periods of time. Think of them as measurements of elapsed time rather than specific points in time. This makes them ideal for calculations involving durations, scheduling, and time-based operations.

Basic Interval Syntax

PostgreSQL provides multiple ways to create intervals. Here's the standard syntax:

-- Basic interval creation
SELECT 'unit quantity'::interval;
 
-- Single unit interval
SELECT '1 year'::interval;-- Results in: 1 year
 
-- Complex interval with multiple units
SELECT '1 year 2 month 3 days 4 hours 5 minutes 6 seconds'::interval;
-- Results in: 1 year 2 mons 3 days 04:05:06
 
-- Alternative compact syntax
SELECT '1 year 2 month 3 days 04:05:06'::interval;
-- Results in: 1 year 2 mons 3 days 04:05:06

-- Basic interval creation
SELECT 'unit quantity'::interval;
 
-- Single unit interval
SELECT '1 year'::interval;-- Results in: 1 year
 
-- Complex interval with multiple units
SELECT '1 year 2 month 3 days 4 hours 5 minutes 6 seconds'::interval;
-- Results in: 1 year 2 mons 3 days 04:05:06
 
-- Alternative compact syntax
SELECT '1 year 2 month 3 days 04:05:06'::interval;
-- Results in: 1 year 2 mons 3 days 04:05:06

Interval Styles

PostgreSQL supports different interval display styles, controlled by the intervalstyle setting:

-- Check current interval style
SHOW intervalstyle; -- Default: postgres
 
-- Change to ISO 8601 format
SET intervalstyle = 'iso_8601';
 
-- Now intervals display in ISO format
SELECT '1 year 2 month 3 days 04:05:06'::interval;
-- Results in: P1Y2M3DT4H5M6S

-- Check current interval style
SHOW intervalstyle; -- Default: postgres
 
-- Change to ISO 8601 format
SET intervalstyle = 'iso_8601';
 
-- Now intervals display in ISO format
SELECT '1 year 2 month 3 days 04:05:06'::interval;
-- Results in: P1Y2M3DT4H5M6S

In ISO 8601 format:

• P indicates the period
• Y represents years
• M represents months
• D represents days
• T separates date and time components
• H represents hours
• M represents minutes
• S represents seconds

Alternative Interval Creation Methods

PostgreSQL offers additional syntax options for creating intervals:

-- Using INTERVAL keyword with quantity
SELECT INTERVAL '2' YEAR; -- Results in: 2 years
 
-- Year-month specification
SELECT INTERVAL '1-6' YEAR TO MONTH; -- Results in: 1 year 6 mons
 
-- Seconds specification
SELECT INTERVAL '6000' SECONDS; -- Results in: 01:40:00

-- Using INTERVAL keyword with quantity
SELECT INTERVAL '2' YEAR; -- Results in: 2 years
 
-- Year-month specification
SELECT INTERVAL '1-6' YEAR TO MONTH; -- Results in: 1 year 6 mons
 
-- Seconds specification
SELECT INTERVAL '6000' SECONDS; -- Results in: 01:40:00

Generated Columns: Automated Calculations

Generated columns in PostgreSQL automatically compute their values based on other columns. PostgreSQL only supports stored generated columns (not virtual).

Basic Generated Column Syntax

Here's a simple example converting height from centi-meters to inches:

CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
 
-- Insert values (only specify the source column)
INSERT INTO people (height_cm) VALUES
    (100),
    (200);
 
-- Query shows both original and generated values
SELECT * FROM people;
/*
 height_cm | height_in
-----------|---------------------
      200  | 78.7401574803149606
      210  | 82.6771653543307087
*/
 
-- Attempting to insert into generated column throws error
INSERT INTO people (height_in) VALUES (70);-- Error

CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
 
-- Insert values (only specify the source column)
INSERT INTO people (height_cm) VALUES
    (100),
    (200);
 
-- Query shows both original and generated values
SELECT * FROM people;
/*
 height_cm | height_in
-----------|---------------------
      200  | 78.7401574803149606
      210  | 82.6771653543307087
*/
 
-- Attempting to insert into generated column throws error
INSERT INTO people (height_in) VALUES (70);-- Error

Practical Example: Email Domain Extraction

A common use case is automatically extracting domains from email addresses:

CREATE TABLE users (
    email text,
    email_domain text GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED
);
 
-- Insert email addresses
INSERT INTO users (email) VALUES
    ('abhishek@gmail.com'),
    ('abhishek@gmail.com');
 
-- Query shows automatic domain extraction
SELECT * FROM users;
/*
 email               | email_domain
---------------------|--------------
 abhishek@gmail.com  | gmail.com
 abhishek@gmail.com  | gmail.com
*/

CREATE TABLE users (
    email text,
    email_domain text GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED
);
 
-- Insert email addresses
INSERT INTO users (email) VALUES
    ('abhishek@gmail.com'),
    ('abhishek@gmail.com');
 
-- Query shows automatic domain extraction
SELECT * FROM users;
/*
 email               | email_domain
---------------------|--------------
 abhishek@gmail.com  | gmail.com
 abhishek@gmail.com  | gmail.com
*/

Important Restrictions on Generated Columns

Generated columns in PostgreSQL have several key restrictions:

1. Row-Level Only: Generated columns can only reference values from the current row. They cannot:
   • Reference other rows
   • Reference other tables
   • Use subqueries
2. Pure Functions: Only deterministic functions are allowed. The function must:
   • Always produce the same output for the same input
   • Not have side effects
   • Not depend on external state
3. No Chaining: Generated columns cannot reference other generated columns

Best Practices for Generated Columns

1. Choose Appropriate Storage: Since PostgreSQL only supports STORED generated columns, ensure you have adequate storage for the generated values.
2. Performance Considerations: Remember that stored generated columns:
   • Take up physical storage space
   • Need to be updated when source columns change
   • Can impact INSERT and UPDATE performance
3. Function Selection: Use only stable, immutable functions for generation expressions to ensure consistent results.
4. Data Type Matching: Ensure the generation expression's result type matches the generated column's declared type to avoid implicit type conversions.

Conclusion

Intervals and Generated Columns are powerful PostgreSQL features that, when used correctly, can significantly enhance your database's functionality:

• Intervals provide a robust way to handle time durations, perfect for scheduling, time calculations, and period management.
• Generated Columns automate calculations and data transformations, ensuring data consistency and reducing application-level computation.

Understanding these features enables you to build more efficient and maintainable database schemas. Remember to consider the storage implications of generated columns and the formatting options available for intervals when implementing these features in your applications.

⏲️