Subject: Re: MS SQL to Postgres

From: Alban Hertroys <[email-masked]>
Date: Wed, 20 Aug 2025 22:14:56 +0200

> On 20 Aug 2025, at 19:25, KK CHN <[email-masked]> 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.

---

From: Justin <[email-masked]>
Date: Wed, 20 Aug 2025 17:31:16 -0400

On Wed, Aug 20, 2025 at 4:15 PM Alban Hertroys <[email-masked]> wrote:

> 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
> --
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

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