How to create created- and updated-columns to database table

Creating a example table

Let’s start with a simple example

1
2
3
4
5
6
7

create table example (
  id serial not null,
  name varchar(256),
  created timestamp default CURRENT_TIMESTAMP,
  updated timestamp default CURRENT_TIMESTAMP,
  primary key(id)
);

We create a created- and updated -columns and set their default value to be current timestamp. If we don’t specify the value for these columns, the value will be defined as a current timestamp.

Creating a update function

1
2
3
4
5
6
7

CREATE FUNCTION update_updated_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated = now();
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

This function will set the new row’s updated-column to current timestamp. Let’s define a trigger, which calls this function.

Creating trigger

1
2
3
4
5

CREATE TRIGGER update_tags_updated
    BEFORE UPDATE
    ON example
    FOR EACH ROW
EXECUTE PROCEDURE update_updated_column();

This trigger will call previous function on every row on example-table which is updated.