SQL Server (MSSQL) Migration to PostgreSQL Timestamp

The article describes how to track the storage records for insert and update timestamps in PostgreSQL using the Virtual columns and Trigger tracking methods.

By Digoal

Background

The data type of SQL Server timestamp is independent of time and date. It is a binary number that indicates the relative order of data modifications that occurred in the database. The timestamp data type was initially introduced to support the SQL Server recovery algorithm. Each time a page is modified, it is marked with the current @@DBTS value, and then this value is increased by one. This is enough to help the recovery process determine the relative order of page modifications, but the timestamp value has nothing to do with the time.

PG supports this function in two methods:

1) Trigger tracking

2) Virtual columns

Method 1: Trigger-Track Timestamp by moddatetime

The default value of the timestamp field is the current time when you use the moddatetime plug-in. This plug-in also tracks and alters the modified value.

For more details, visit this link.

F.36.4. moddatetime - Functions for Tracking Last Modification Time

You can use the moddatetime() plug-in to save the current time into a timestamp field. Tracking the last modified time of a specific row in a table is straightforward using this plug-in.

Creating a BEFORE UPDATE trigger using this function allows you to utilize it. You should define a single trigger argument: the name of the column to be altered. The type of this column should be timestamp or timestamp with the time zone.

Here is an example of moddatetime:

DROP TABLE mdt;  
  
CREATE TABLE mdt (  
        id              int4,  
        idesc           text,  
        moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL  
);  
  
CREATE TRIGGER mdt_moddatetime  
        BEFORE UPDATE ON mdt  
        FOR EACH ROW  
        EXECUTE PROCEDURE moddatetime (moddate);  
  
INSERT INTO mdt VALUES (1, 'first');  
INSERT INTO mdt VALUES (2, 'second');  
INSERT INTO mdt VALUES (3, 'third');  
  
SELECT * FROM mdt;  
  
UPDATE mdt SET id = 4  
        WHERE id = 1;  
UPDATE mdt SET id = 5  
        WHERE id = 2;  
UPDATE mdt SET id = 6  
        WHERE id = 3;  
  
SELECT * FROM mdt;

The results of the above example are as follows:

db1=# create extension moddatetime ;  
CREATE EXTENSION  
db1=# DROP TABLE mdt;  
  
CREATE TABLE mdt (  
        id              int4,  
        idesc           text,  
ERROR:  table "mdt" does not exist  
db1=#   
db1=# CREATE TABLE mdt (  
db1(#         id              int4,  
db1(#         idesc           text,  
db1(#         moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL  
db1(# );  
CREATE TABLE  
db1=#   
db1=# CREATE TRIGGER mdt_moddatetime  
db1-#         BEFORE UPDATE ON mdt  
db1-#         FOR EACH ROW  
db1-#         EXECUTE PROCEDURE moddatetime (moddate);  
CREATE TRIGGER  
db1=#   
db1=# INSERT INTO mdt VALUES (1, 'first');  
INSERT 0 1  
db1=# INSERT INTO mdt VALUES (2, 'second');  
INSERT 0 1  
db1=# INSERT INTO mdt VALUES (3, 'third');  
INSERT 0 1  
db1=#   
db1=# SELECT * FROM mdt;  
  
 id | idesc  |          moddate             
----+--------+----------------------------  
  1 | first  | 2020-02-13 13:57:28.018714  
  2 | second | 2020-02-13 13:57:28.019169  
  3 | third  | 2020-02-13 13:57:28.019491  
(3 rows)  
  
db1=#   
db1=# UPDATE mdt SET id = 4  
db1-#         WHERE id = 1;  
UPDATE 1  
db1=# UPDATE mdt SET id = 5  
db1-#         WHERE id = 2;  
UPDATE 1  
db1=# UPDATE mdt SET id = 6  
db1-#         WHERE id = 3;  
UPDATE 1  
db1=#   
db1=# SELECT * FROM mdt;  
 id | idesc  |          moddate             
----+--------+----------------------------  
  4 | first  | 2020-02-13 13:57:28.107631  
  5 | second | 2020-02-13 13:57:28.118198  
  6 | third  | 2020-02-13 13:57:28.152911  
(3 rows)

moddatetime plug-in applies to all PostgreSQL versions, as long as it is available.

When you insert a record, the value of the timestamp field is the default current time. After you update it, this value becomes the time of change in the record.

Method 2: Virtual Columns

For more details, visit this link.

Outlined below are the features introduced in PostgreSQL 12:

  GENERATED ALWAYS AS ( generation_expr ) STORED |  
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |

GENERATED ALWAYS AS (generation_expr) STORED

Creating a column as a ‘generated column’ is possible with the help of this clause. This column doesn’t allow explicit insert and is returnable upon reading the output of the given expression.

The keyword 'STORED' indicates that the column will be saved on the disk and processed on write operation.

With this generation expression, you can point out other columns in the table. However, it is incapable of referring to other ‘generated’ columns. Any function or operator that you use should be immutable. Further, references to other tables are not permissible.

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
.
You can create the new column as an “identify column” using this clause. An implicit sequence is typically associated with this clause, helping populate values automatically in the columns of new rows.

ALWAYS and BY DEFAULT clause helps you understand the priority order of sequence value and user-specific value in an INSERT statement. With the ALWAYS clause listed, a user-specified value is only allowed when OVERRIDING SYSTEM VALUE is mentioned in the INSERT statement. In contrast, the user-specified value gets precedence when BY DEFAULT is specified. Check INSERT for more information (With COPY command, these settings become irrelevant as user-specific values always get priority). You can also override these sequence settings with the help of the optional sequence_options clause.

An alternate option is GENERATED ALWAYS AS ( generation_expr ) STORED. The following part outlines few examples:

If you want to track the time, create an immutable function that returns a time type.

db1=# create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict immutable;  

CREATE FUNCTION  
  
db1=# select im_now();  
            im_now               
-------------------------------  
 2020-02-13 14:06:12.338434+08  
(1 row)

Track the mod_time field. When inserting and updating, use the value of im_now.

mod_time timestamp GENERATED ALWAYS AS (im_now()) stored  

db1=# create table t1 (id int primary key, info text, crt_time timestamp, 
mod_time timestamp GENERATED ALWAYS AS (im_now()) stored);  
CREATE TABLE  
db1=#   
db1=# insert into t1 (id, info, crt_time) values (1,'test', now());  
INSERT 0 1  
db1=# select * from t1;  
 id | info |          crt_time          |          mod_time            
----+------+----------------------------+----------------------------  
  1 | test | 2020-02-13 14:06:57.287431 | 2020-02-13 14:06:57.287431  
(1 row)  
  
db1=# update t1 set info='a' where id=1;  
UPDATE 1  
db1=# select * from t1;  
 id | info |          crt_time          |          mod_time            
----+------+----------------------------+----------------------------  
  1 | a    | 2020-02-13 14:06:57.287431 | 2020-02-13 14:07:10.403902  
(1 row)

You cannot directly write or update the value of the stored virtual column field.

db1=# update t1 set info='a',mod_time=now() where id=1;  
ERROR:  column "mod_time" can only be updated to DEFAULT  
DETAIL:  Column "mod_time" is a generated column.  
  
db1=# update t1 set info='a',mod_time=default where id=1;  
UPDATE 1  
db1=# select * from t1;  
 id | info |          crt_time          |          mod_time            
----+------+----------------------------+----------------------------  
  1 | a    | 2020-02-13 14:06:57.287431 | 2020-02-13 14:07:58.252205  
(1 row)  
  
db1=# insert into t1 (id,info,crt_time,mod_time) values (2,'abc',now(),now());  
ERROR:  cannot insert into column "mod_time"  
DETAIL:  Column "mod_time" is a generated column.

Note that mod_time int8 GENERATED ALWAYS AS IDENTITY is not suitable for tracking. However, it is helpful for sequence generation. This is because the values are not revised during the record update.

db1=# create table t(id int primary key, info text, crt_time timestamp, 
mod_time int8 GENERATED ALWAYS AS IDENTITY);  
CREATE TABLE  
db1=# insert into t (id, info, crt_time) values (1,'test',now());  
INSERT 0 1  
db1=# select * from t;  
 id | info |         crt_time         | mod_time   
----+------+--------------------------+----------  
  1 | test | 2020-02-13 14:04:43.5781 |        1  
(1 row)  
  
db1=# update t set info='abc' where id=1;  
UPDATE 1  
db1=# select * from t;  
 id | info |         crt_time         | mod_time   
----+------+--------------------------+----------  
  1 | abc  | 2020-02-13 14:04:43.5781 |        1  
(1 row)

If the value you want to track is not a timestamp but a sequence, change the stored expression function. Here is an example:

create sequence seq;  
  
create or replace function im_seq () returns int8 as $$  
  select nextval('seq'::regclass);  
$$ language sql strict immutable;  
  
db1=# select im_seq();  
 im_seq   
--------  
      1  
(1 row)  
  
db1=# select im_seq();  
 im_seq   
--------  
      2  
(1 row)  
  
create table t2 (id int primary key, info text, crt_time timestamp, 
mod_time int8 GENERATED ALWAYS AS (im_seq()) stored);  
  
db1=# insert into t2 values (1,'test',now());  
INSERT 0 1  
db1=# select * from t2;  
 id | info |          crt_time          | mod_time   
----+------+----------------------------+----------  
  1 | test | 2020-02-13 14:40:54.107082 |        3  
(1 row)  
  
db1=# update t2 set info='a';  
UPDATE 1  
db1=# select * from t2;  
 id | info |          crt_time          | mod_time   
----+------+----------------------------+----------  
  1 | a    | 2020-02-13 14:40:54.107082 |        4  
(1 row)

Summary

The timestamp data type was initially designed to support the SQL Server recovery algorithm. If your business does not use the timestamp field of SQL Server, you shouldn’t worry. It is unnecessary as the logic replication, timestamp recovery, and flashback features of PostgreSQL do not rely on this field.

The trigger and virtual columns of PostgreSQL are similar to SQL Server timestamps. If needed, you can consider using them.

References

1) https://www.postgresql.org/docs/12/sql-createtable.html

2) https://www.postgresql.org/docs/12/contrib-spi.html#id-1.11.7.45.8

Community

SQL Server (MSSQL) Migration to PostgreSQL Timestamp

Background

Method 1: Trigger-Track Timestamp by moddatetime

Method 2: Virtual Columns

Summary

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

ApsaraDB RDS for SQL Server

AnalyticDB for PostgreSQL

ActionTrail