3

I have migrated a database from SQL Server into PostgreSQL. A number of tables contain a ts column(timestamp) which stores the rowversion and is autogenerated in SQL server.

When I converted the stored procedures into PostgreSQL, I found that insert procedures are failing because the ts column (converted to bytea in PostgreSQL) is NOT NULL and no explicit value is being inserted in this column.

Now, I need to implement the same in PostgreSQL as well. I am assuming encoding the current datetime into bytea somehow will work correctly. Let me know if there is a way to implement this.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Krishan Jangid
  • 75
  • 7
  • I don't think there is any way to convert SQL Servers really, really strange "timestamp" data type to anything in Postgres. Just use a real `timestamp` column –  Feb 25 '19 at 06:50
  • @a_horse_with_no_name I was able to convert bytea into integer successfully by using : ('x'||lpad(encode(ts::bytea, 'hex'), 16, '0'))::bit(64)::bigint Can't it be reversed? – Krishan Jangid Feb 25 '19 at 06:55
  • I was thinking of encoding current epoch time (bigint) into bytea if above operation could be reversed. – Krishan Jangid Feb 25 '19 at 06:56

1 Answers1

1

For a timestamp, timestamp with time zone is the best data type.

If you want it filled automatically, set the DEFAULT value of the column to current_timestamp.

There is nothing that keeps you from casting a timestamp first to text and then to bytea, but what would be gained that way?

Reading up about Microsoft SQL Server's timestamp data type, it seems like all you want is a unique identifier for a row version that changes whenever the row is modified.

In PostgreSQL this happens automatically anyway. So you don't need a special column, just use the combination of xmin and ctid. Both are system columns. If either of these numbers change, the row has been modified.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    SQL Server's `timestamp` is everything but a timestamp. The name is complete bollocks. It's some kind of internal "row version" that is encoded in binary. Maybe it is seeded with a timestamp as the input, but despite the completely confusing name is has nothing to do with a real "timestamp". –  Feb 25 '19 at 06:51
  • Thanks for the explanation, it enabled me to make the answer more useful. – Laurenz Albe Feb 25 '19 at 07:04
  • @LaurenzAlbe, using the system column for this makes sense. However, can you please explain the reason of using combination of `xmin` and `ctid`? Will there be any issue if I just use `xmin`? – Krishan Jangid Feb 26 '19 at 16:35