This is the 16th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

🤞 personal home page: @Qingcheng sequence member Stone 🤞 fan benefits: plus one to one fan group to answer questions, get free rich resume template, improve learning materials, do a good job in the new era of volume king!

When creating a Schema using PostgreSQL, some interns were not clear about the automatic update timestamp of create_AT and update_AT fields. Take note of this article and provide a reference for future interns and new colleagues.

First, why

Before we talk about how to update timestamps, we need to understand why automatic timestamps are necessary. For many business tables, most of us need to record the following fields:

  • create_atCreation time
  • update_atUpdate time
  • create_byfounder
  • update_byUpdate one

To assign values to these fields, we need to assign values to the entity in the Repository layer. The creation time and update time take the current system time LocalDateTime, and the creator and updater need to assign values to the system user. Is it possible that fields created and updated at a time that is irrelevant to the current business are not manually assigned on Repository each time?

Of course, there must be. The creation time is simply the time when the data is inserted into the row, and the update time is the time when the data is updated.

For Mysql, the built-in functions are fairly easy to create and update fields automatically, but for PostgreSQL things are a little more complicated.

Two, how to do

To automatically populate the create_AT column when inserting data, we can use the DEFAULT value, as shown below.

CREATE TABLE users (
  ...
  create_at timestamp(6) default current_timestamp
)
Copy the code

Setting a default value current_TIMESTAMP for the create_AT field results in explicitly overwriting the value of the column by providing the value in the INSERT statement.

However, this method is only useful for insert rows. If we update rows, we need to use the database trigger.

First we write a trigger update_MODIfied_column as shown below, which updates the table field update_AT to the current timestamp.

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';
Copy the code

And then we apply this trigger, how do we apply it? Set the trigger condition for the trigger, of course.

CREATE TRIGGER update_table_name_update_at BEFORE UPDATE ON table_name FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();
Copy the code

Update table table_name (table_name, table_name, table_name, table_name) At this point, achieve the goal.

Boy, haven’t you seen enough? Click on the details of the stone, casually have a look, maybe there is a surprise? Welcome to support the likes/attention/comments, your support is my biggest motivation, thank you!