Here's how that looks: INSERT INTO client (firstname, lastname ) With PostgreSQL, you need to modify your INSERT statement, adding a line that specifies the column value you need returned. MS SQL Server provides SCOPE_IDENTITY() for this purpose, and some programming languages will return the generated identifier value automatically following an INSERT query. For example, if you insert a new client, you probably want to know the client's ID. When an INSERT statement generates an identity value, applications frequently have need to retrieve and use that generated identifier. Use RETURNING with INSERT to retrieve identity So we had to go through updating queries from SELECT TOP 1 to SELECT. Postgres, like MySQL, applies limits to the result set at the end of the SQL statement using LIMIT, while SQL Server does this at the outset, using TOP. Visual Studio Code's shortcut Command-K-L (⌘+K+L) came in very handy.
#POSTGRESQL VS SQL SERVER CODE#
We worked our way through the code base, lowercasing as we went. At the database level, this was done by the data transfer tool mentioned earlier, but within the application, we had to do it manually. The approach we took was to lowercase all identifers. There are numerous discussions about this online, so I won't repeat them. PostgreSQL handles the casing of identifiers differently than SQL Server, which is to say, case matters. In no particular order: Lowercase table and column names While not comprehensive, here are some of the differences between SQL Server and Postgres that we needed to account for in our applications. The more labor intensive part of changing database engines was updating our application code to be PostgreSQL compatible. A few dry runs to testing servers were necessary to work out the exact settings and process, but we were very happy with the results. The data transfers was fast, it retained foreign keys and indexes, and allowed a high degree of control over the operation and handling of tables and columns.
I thought this would be the hard part, but the actual migration of the data from one database engine to the other turned out to be rather straightforward, thanks to the aptly named tool: MS SQL to PostgreSQL. So, for context, this was a migration from a dedicated instance of MS SQL Server 2016, as well as cloud-based Azure SQL Databases to DigitalOcean's managed PostgreSQL hosting, running PostgreSQL v11. That is, comparing PG 9.3 with SQL Server 2014 just isn't going to paint an accurate picture of where they stand today (February 2020). Many of the articles that I came across comparing SQL Server and Postgres, aside from those that were useless from a practical standpoint, were outdated. Use RETURNING with INSERT to retrieve identity.Updating Application Code for PostgreSQL.But there are some points and resources that will hopefully be helpful. Is it everything you need to know? Absolutely not. The following are some notes and observations from a recent, successful, migration from Microsoft SQL Server to PostgreSQL. I'm not a database guru or SQL sherpa, but from time to time I do fill the role of de facto DBA.