While you can create a default date value with now(), sometimes you don't want the user to be able to override that date. You also may want to have automatic updated_at date values.

First, create these functions which can be used on any table:

Reusable Procedures#

	-- PROCEDURES for created_at and updated_at
CREATE OR REPLACE FUNCTION update_dates()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    NEW.created_at = OLD.created_at;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION insert_dates()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = now();
    NEW.updated_at = NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Next, create the function triggers for the specific tables you want to protect your dates with:

Functions for Table X#

	DROP TRIGGER IF EXISTS profiles_update_dates
  ON profiles;
CREATE TRIGGER profiles_update_dates
BEFORE UPDATE ON profiles
FOR EACH ROW EXECUTE PROCEDURE update_dates();

DROP TRIGGER IF EXISTS profiles_insert_dates
  ON profiles;
CREATE TRIGGER profiles_insert_dates
BEFORE INSERT ON profiles
FOR EACH ROW EXECUTE PROCEDURE insert_dates();

And you can just create new functions for every Table X, reusing the procedures above.

For the full thought process on this, see my original article.

Now, it is impossible for anyone to update updated_at or created_at manually. If you want to set a custom published date, I suggest you have a third non-protect field called published_at. This could be useful for when you want to write a post and publish it later.

Hope this helps,

J