Best practice: all Postgres tables can have these columns

They should all have these columns:

• Primary key: id , bigserial (which creates a sequence behind the scene)
• Date created: utc_created – Use current timestamp as a default value
• Date updated: utc_updated – Use a trigger to automatically update it (See code below)
• Creator: created_by – text type, can be anything
• Updator: updated_by – text type , can be anything

Code:

create table my_table
(
    id          bigserial      not null ,
    
    utc_created timestamptz not null default current_timestamp,
    created_by  text        not null,
    utc_updated timestamptz null,
    updated_by  text        null,

    primary key (id)
);


CREATE TRIGGER my_table_updated_by_trigger BEFORE UPDATE
    ON my_table FOR EACH ROW EXECUTE PROCEDURE
    update_utc_updated_column(); -- see below

-- See https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/

CREATE OR REPLACE FUNCTION update_utc_updated_column()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.utc_updated = current_timestamp;
    RETURN NEW;
END;
$$ language 'plpgsql';