PostgreSQL Auto-Update `created_at` Trigger

created_at_trigger.md

PostgreSQL Auto-Update created_at Trigger

This guide explains how to automatically add a trigger to update the created_at column in PostgreSQL whenever a table is created or modified to include that column.

Overview

• When a table is created or altered to include a created_at column, a trigger is automatically added.
• The trigger ensures that created_at is set to NOW() on INSERT.
• An event trigger listens for CREATE TABLE and ALTER TABLE commands and applies the logic.

Step 1: Create Function to Ensure Trigger

CREATE OR REPLACE FUNCTION ensure_created_at_trigger()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE 
    table_name TEXT;
    trigger_name TEXT;
    column_exists BOOLEAN;
BEGIN
    -- Get the affected table name
    SELECT event_object_table INTO table_name
    FROM information_schema.columns
    WHERE column_name = 'created_at'
    AND event_object_table = (SELECT tgrelid::regclass::text FROM pg_trigger WHERE tgname = 'ensure_created_at_trigger' LIMIT 1);

    -- Check if the table has the 'created_at' column
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = table_name AND column_name = 'created_at'
    ) INTO column_exists;

    -- If the column exists, add a trigger to auto-update 'created_at'
    IF column_exists THEN
        trigger_name := table_name || '_created_at_trigger';

        -- Check if the trigger already exists to avoid duplicate triggers
        IF NOT EXISTS (
            SELECT 1 FROM pg_trigger 
            WHERE tgname = trigger_name
        ) THEN
            EXECUTE format($$
                CREATE OR REPLACE FUNCTION update_created_at_column()
                RETURNS TRIGGER AS $$
                BEGIN
                    IF NEW.created_at IS NULL THEN
                        NEW.created_at := now();
                    END IF;
                    RETURN NEW;
                END;
                $$ LANGUAGE plpgsql;
            $$);

            EXECUTE format($$
                CREATE TRIGGER %I 
                BEFORE INSERT ON %I
                FOR EACH ROW 
                EXECUTE FUNCTION update_created_at_column();
            $$, trigger_name, table_name);
        END IF;
    END IF;
END;
$$;

Step 2: Create Event Trigger to Detect Schema Changes

CREATE OR REPLACE FUNCTION detect_column_addition()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM ensure_created_at_trigger();
END;
$$;

CREATE EVENT TRIGGER add_created_at_trigger
ON ddl_command_end
WHEN TAG IN ('ALTER TABLE', 'CREATE TABLE')
EXECUTE FUNCTION detect_column_addition();

How It Works

1. Whenever a table is created or altered to include created_at, the ensure_created_at_trigger function checks for the column.
2. If created_at exists, a new trigger is added to ensure it is set on INSERT.
3. The event trigger add_created_at_trigger listens for DDL changes (CREATE TABLE, ALTER TABLE) and applies the logic.

Testing

1. Create a table with created_at:

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

   � The trigger should automatically be added.

2. Insert data:

   INSERT INTO users (name) VALUES ('John Doe') RETURNING *;

   â�� The created_at column should be automatically filled with the current timestamp.

Summary

This approach ensures that any table with a created_at column automatically receives a trigger to populate it on insert, reducing the need for manual trigger creation.

Hey, wouldn't updated_at be a better column name? As created_at shouldnt be changed at all in my opinion