Timestamp Data Type Conversion from Microsoft SQL Server to PostgreSQL

In Microsoft SQL Server timestamp is the synonym for the rowversion data. Rowversion is a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

PostgreSQL doesn’t support the Timestamp datatype, that’s why it requires significant code changes. Let's consider the following piece of code:

 CREATE TABLE dbo.ExampleTable
 (
  PriKey INT  NOT NULL,
  timestamp TIMESTAMP  NOT NULL
 );   
 
 ALTER TABLE dbo.ExampleTable ADD  CONSTRAINT PK__ExampleT__0DAA475129938053
 PRIMARY KEY (PriKey); 

In this case an analogue of the timestamp data type in PostgreSQL can be the bytea data type, and the value increment when inserting or updating data in a table is added using a trigger.

You will get the following result:

 CREATE TABLE dbo.ExampleTable
 (
    PriKey INTEGER  NOT NULL,
    timestamp BYTEA  NOT NULL
 );  
 
 ALTER TABLE dbo.ExampleTable add CONSTRAINT PK__ExampleT__0DAA475129938053
 PRIMARY KEY(PriKey);
 
 create function swf_increment_timestamp_exampletable_timestamp()
   returns trigger
 as
 $$
 begin
       IF exists(select 1 from exampletable) then
         new.timestamp := int8send((select max ('x'||lpad(encode(timestamp, 'hex'), 16, '0'))::bit(64)::bigint from exampletable)+1::bigint);
         return new;
     else
         new.timestamp := int8send(1);
         return new;
     end if;
 end;
 $$
 language plpgsql;

 create trigger swt_timestamp_trigger_exampletable_timestamp
   before insert or update on ExampleTable
   for each row
 execute procedure swf_increment_timestamp_exampletable_timestamp();

Let's compare the results.

Microsoft SQL Server	PostgreSQL
select * from ExampleTable	select prikey, encode(timestamp::bytea, 'hex') from ExampleTable
insert into ExampleTable(PriKey) values (3) select * from ExampleTable	insert into ExampleTable(PriKey) values (3) select prikey, encode(timestamp::bytea, 'hex') from ExampleTable
update ExampleTable set PriKey = 7 where PriKey = 3 select * from ExampleTable	update ExampleTable set PriKey = 7 where PriKey = 3 select prikey, encode(timestamp::bytea, 'hex') from ExampleTable

As you can see, this solution completely repeats the logic of the Timestamp data type.

This solution is automatically executed by our software SQLWays Toolkit. Each column of the timestamp data type will be converted to a column of the bytea data type. Trigger will be created for each such column. In the trigger, the value of the column will be incremented. This trigger will work for each row when data is inserted or updated.

You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com