Shulou(Shulou.com)06/02 Report--

This article mainly introduces how PostgreSQL automatically updates the timestamp, the article is very detailed, has a certain reference value, interested friends must read it!

What is PostgreSQL timestamp data type?

In PostgreSQL, the next data type is TIMESTAMP, which can store TIME and DATE values. It does not support any time zone data. This means that when we switch the time zone of the database server, the timestamp value will be stored in the database and cannot be modified repeatedly.

In PostgreSQL, the TIMESTAMP data type is divided into two temporal data types, as follows:

timestamp

timpstamptz

Timestamp: : Use timestamp data type without time zone.

Timestamptz: : timestamptz data type is used with time zones.

Note:

The timestamptz data type is a date and time data type associated with a time zone and is a timestamp with a time zone.

Timestamps and Timestamps store 8 bytes of timestamp values as shown in the following command:

SELECT typname, typlenFROM pg_typeWHERE typname ~ '^timestamp';

Recently, the company has had some trainees who are not clear about the automatic update timestamp of the create_at and update_at fields when creating table schemas using PostgreSQL. Write down this article for future interns and new colleagues to refer to.

I. Why

Before we talk about how to update timestamps, let's understand why it's necessary to update timestamps automatically. For many business tables, most of us need to record the following fields:

create_at Create time

update_at update time

create_by Creator

update_by Update

In order to assign values to these fields, we need to assign values to entities at the repository level. The creation time and update time take the current system time LocalDateTime. The creator and updater need to be assigned values by the system user. For creation time and update time, is it possible not to manually assign values to fields unrelated to the current business every time on the repository?

Of course, there must be. The creation time is nothing more than the time when the data is newly inserted into the row, and the update time is the time when the row data is updated. If you understand the meaning of this layer, there will be a solution.

For Mysql, the built-in functions are fairly easy for automatic updates of the creation time and update time fields, but for PostgreSQL things are a little more complicated.

II. How to do it

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)

Set a default value current_timestamp for the create_at field, which explicitly overrides the column's value by supplying a value in the INSERT statement.

But the above method only works when inserting row data. If we update rows, we need to use trigger to database.

First we write a trigger update_modified_column as shown in the following code, which means that the update table field update_at is the current timestamp.

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

And then we apply this trigger. How do we apply it? The trigger condition is set for this trigger, of course.

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

When updating table_name row data, execute this trigger, we need to apply this trigger for each table setting! So far, the goal has been achieved.

The above is all the content of "PostgreSQL how to automatically update timestamps" this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.