Sql-server – Migrating from PostgreSQL to SQL Server

migrationpostgresqlsql serversql-server-2008-r2

I have been tasked to migrate a database from PostgreSQL 9 to SQL Server 2008 R2, and I have found some issues I need to address. I have quite limited experiences with PostgreSQL I'm afraid.

  1. Most keys are BigInt. In PostgreSQL a function is called to get the next ID and this is done in advance in the code for whatever reason. Can this be done in the same manner in SQL Server or am I forced to create an key index table to keep track of the current ID. IDENTITY(1,1) will afaik deliver a new ID on the insert.

  2. There are some stored procedures that returns NEW as a TRIGGER. What would be the equivalent in the SQL Server language?

  3. What impact has the difference in triggers given that PostgreSQL triggers are row based?

Best Answer

  1. You can create your keys using a SEQUENCE. Every time you need a new ID from the sequence, you just have to issue SELECT NEXT VALUE FOR SomeSequence;. Unfortunately not all versions of SQL Server support sequences (it's 2012 and up), so if you're stuck with SQL Server 2008 R2 you will have to code it by yourself.

    See Sequence Tables by Paul White for an example.

  2. As far as I know, returning NEW from a BEFORE trigger means the INSERT or UPDATE operation that originally fired the trigger will take place. The trigger function may change the column values of it. For a description and examples check the documentation.

    You can achieve the same thing in SQL Server with regular DML statements such as UPDATEs and INSERTs, joining the destination table with the logical tables INSERTED and DELETED. You could also decide to implement the operation on behalf of the original statement, using an INSTEAD OF trigger.

  3. Triggers in Postgres are either row-based or statement-based. Translating FOR EACH ROW into joins to INSERTED/DELETED is just a matter of using the correct keys to correlate the tables.

Related Question