Supabase•4w ago

How to update the updated_at field automatically

Hey! I'm working with supabase database and I need to create new table with update_at field and that field will be updated automatically on new change. I wondering if there is a better way to enable it rather than that I'll implement complete trigger snippet for that. I'll probably need it for multiple migrations. I found that postgresSQL have this modadatetime extension but I'm ensure if it should work out of the box with the latest postgres. https://www.postgresql.org/docs/current/contrib-spi.html#CONTRIB-SPI-MODDATETIME

PostgreSQL Documentation

F.41. spi — Server Programming Interface features/examples

F.41. spi — Server Programming Interface features/examples # F.41.1. refint — Functions for Implementing Referential Integrity F.41.2. autoinc — Functions for …

5 Replies

garyaustin•4w ago

A trigger is the normal way. Moddatetime is a trigger being added.

JacobOP•4w ago

I see that the extension is available in supabase cloud but not enabled should this new migration work?

-- Enable moddatetime extension (needed for auto-updating updated_at)
CREATE EXTENSION IF NOT EXISTS moddatetime;

CREATE TABLE IF NOT EXISTS table1 (
  updated_at TIMESTAMPTZ DEFAULT NOW(),
);

-- Automatically update "updated_at" on row changes using moddatetime extension
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON table1
FOR EACH ROW
EXECUTE FUNCTION moddatetime(updated_at);

-- Enable moddatetime extension (needed for auto-updating updated_at)
CREATE EXTENSION IF NOT EXISTS moddatetime;

CREATE TABLE IF NOT EXISTS table1 (
  updated_at TIMESTAMPTZ DEFAULT NOW(),
);

-- Automatically update "updated_at" on row changes using moddatetime extension
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON table1
FOR EACH ROW
EXECUTE FUNCTION moddatetime(updated_at);

garyaustin•4w ago

You can just enable in the dashboard... I'd have to go read the extension info again, I used it a couple of years ago. BUT I would typically plan to have a created_at and and ID column I don't want the user to change or other such columns so usually just role the updated_at into the same trigger for everything.

JacobOP•4w ago

You can just enable in the dashboard...

It's nice that the whole infra can be declared from the migratinos Like creating a buckets, enabling extensions

garyaustin•4w ago

If you are using migrations that is certainly the way to go to allow moving to other instances easier.

Gaming

Programming

How to update the updated_at field automatically

Did you find this page helpful?