Re: MS SQL to Postgres - Mailing list pgsql-general
| From | Justin |
|---|---|
| Subject | Re: MS SQL to Postgres |
| Date | |
| Msg-id | CALL-XeMP5y29vJjccVmbpV3azRj3KECgdS8mzXHG1iJn9+RdSA@mail.gmail.com Whole thread Raw |
| In response to | Re: MS SQL to Postgres (Alban Hertroys <haramrae@gmail.com>) |
| List | pgsql-general |
On Wed, Aug 20, 2025 at 4:15 PM Alban Hertroys <haramrae@gmail.com> wrote:
> On 20 Aug 2025, at 19:25, KK CHN <kkchn.in@gmail.com> wrote:
(…)
> 4. Any hurdles or challenges or risks
MS SQL defaults to case insensitive string comparisons, trimming trailing white-space.
PostgreSQL defaults to case sensitive string comparisons, so incorrectly cased strings in queries that match in MS SQL will not match in PostgreSQL.
The trailing spaces bit is not going to matter while moving the data to Postgres, as you will not get any trailing spaces from MS SQL to be stored in PostgreSQL (they’ve been trimmed already, after all) - but it could trigger some application bugs where people have assumed that trailing spaces get trimmed.
Also, time zone names are wildly different between the two. MS SQL uses Microsoft Windows time zone names, Postgres (and most other RDBMSes) use IANA names.
Alban Hertroys
--
There is always an exception to always.
Moving the data and schema are the easy part, it's all minor differences in the SQL implementation that bite big time.
CASE SENSITIVE vs CASE INSENSITIVE for searching
CASE SENSITIVE vs CASE INSENSITIVE for searching
Sessions/Connections can't jump databases in PostgreSQL have to create a new connection while in MSSQL if the user has permissions can connect to any database using fully qualified names database.schema.table. This is not possible in PostgreSQL there are workarounds using FDW, which is hackish.
PostgreSQL object names are case insensitive unless using double quotes. example MyTable == mytable to make case sensitive have to use double quote like so SELECT * FROM "MyTable"
Name of common functions differ LEN() == LENGTH() there are lots of these..
LIMIT OFFSET are completely different structure
How Transactions are handled you need to read up on PostgreSQL MVCC vs the MSSQL default transaction handling and Isolation level. MSSQL can be made to work like MVCC via SNAPSHOT isolation; it has to be turned on as its off by default.
Depending on how MSSQL is being used the locking behavior can be very different. Read up on pessimistic vs optimistic locking, PostgreSQL operates in optimistic locking mode by default, while MSSQL operates in a pessimistic locking mode by default.
PostgreSQL can not read rows/transactions that have NOT been committed, this is possible in MSSQL with "TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" PostgreSQL will ignore that command....
Postgresql operates in Implicit Transaction mode means every command is treated as a separate transaction unless it sees a BEGIN. While MSSQL does not operate that way it expects to see a BEGIN. MSSQL can automatically add the BEGIN using the SET IMPLICIT_TRANSACTIONS ON
There are a bunch of gotchas like this that are not found during testing unless you are looking for them..
Thank you
Justin
pgsql-general by date: