MIME-Version: 1.0
References: <CAKgGyB8ZZLzDVg3eCym=BMf7+8LOS-bfOE1EdXWA_X4qha_=Xw@mail.gmail.com>
 <48AA15E9-1979-489D-B94E-5FE2972A6AD2@gmail.com>
In-Reply-To: <48AA15E9-1979-489D-B94E-5FE2972A6AD2@gmail.com>
From: Justin <zzzzz.graf@gmail.com>
Date: Wed, 20 Aug 2025 17:31:16 -0400
Message-ID: <CALL-XeMP5y29vJjccVmbpV3azRj3KECgdS8mzXHG1iJn9+RdSA@mail.gmail.com>
Subject: Re: MS SQL to Postgres
To: Alban Hertroys <haramrae@gmail.com>
Cc: KK CHN <kkchn.in@gmail.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000001c09a0063cd2b2c1"
Archived-At: <https://www.postgresql.org/message-id/CALL-XeMP5y29vJjccVmbpV3azRj3KECgdS8mzXHG1iJn9%2BRdSA%40mail.gmail.com>
Precedence: bulk

--0000000000001c09a0063cd2b2c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Aug 20, 2025 at 4:15=E2=80=AFPM Alban Hertroys <haramrae@gmail.com>=
 wrote:

>
> > On 20 Aug 2025, at 19:25, KK CHN <kkchn.in@gmail.com> wrote:
>
> (=E2=80=A6)
>
> > 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 PostgreSQ=
L.
>
> 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 store=
d
> in PostgreSQL (they=E2=80=99ve 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

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 =3D=3D mytable  to make case sensitive have to use double
quote like so SELECT * FROM  "MyTable"

Name of common functions differ LEN() =3D=3D LENGTH()  there are lots of th=
ese..

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

--0000000000001c09a0063cd2b2c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 20, 202=
5 at 4:15=E2=80=AFPM Alban Hertroys &lt;<a href=3D"mailto:haramrae@gmail.co=
m">haramrae@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><br>
&gt; On 20 Aug 2025, at 19:25, KK CHN &lt;<a href=3D"mailto:kkchn.in@gmail.=
com" target=3D"_blank">kkchn.in@gmail.com</a>&gt; wrote:<br>
<br>
(=E2=80=A6)<br>
<br>
&gt; 4. Any hurdles or challenges or risks <br>
<br>
MS SQL defaults to case insensitive string comparisons, trimming trailing w=
hite-space.<br>
<br>
PostgreSQL defaults to case sensitive string comparisons, so incorrectly ca=
sed strings in queries that match in MS SQL will not match in PostgreSQL.<b=
r>
<br>
The trailing spaces bit is not going to matter while moving the data to Pos=
tgres, as you will not get any trailing spaces from MS SQL to be stored in =
PostgreSQL (they=E2=80=99ve been trimmed already, after all) - but it could=
 trigger some application bugs where people have assumed that trailing spac=
es get trimmed.<br>
<br>
Also, time zone names are wildly different between the two. MS SQL uses Mic=
rosoft Windows time zone names, Postgres (and most other RDBMSes) use IANA =
names.<br>
<br>
Alban Hertroys<br>
--<br>
There is always an exception to always.<br>
<br><br></blockquote><div><br></div><div>Moving the data and schema are the=
 easy part,=C2=A0 it&#39;s all minor differences in the SQL implementation =
that bite big time.=C2=A0 <br><br>CASE SENSITIVE vs CASE INSENSITIVE for se=
arching=C2=A0<br><br></div><div>Sessions/Connections can&#39;t jump databas=
es 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 da=
tabase.schema.table.=C2=A0 This is not possible in PostgreSQL=C2=A0 there a=
re workarounds=C2=A0using FDW, which is hackish.</div><div><br>PostgreSQL o=
bject names are case insensitive unless using double quotes.=C2=A0 example=
=C2=A0 =C2=A0MyTable =3D=3D mytable=C2=A0 to make case sensitive have to us=
e double quote like so SELECT * FROM=C2=A0 &quot;MyTable&quot;<br><br>Name =
of common=C2=A0functions differ LEN() =3D=3D LENGTH()=C2=A0 there are lots =
of these..<br>=C2=A0<br>LIMIT OFFSET=C2=A0are completely different structur=
e<br><br>How Transactions are handled=C2=A0 you need to read up on PostgreS=
QL MVCC vs the MSSQL default transaction handling and Isolation level.=C2=
=A0 MSSQL can be made to work like MVCC via SNAPSHOT isolation; it has to b=
e turned on as its off by default.<br><br>Depending on how MSSQL is being u=
sed the locking behavior can be very different. Read up on pessimistic=C2=
=A0vs optimistic locking,=C2=A0 PostgreSQL operates in optimistic locking m=
ode by default,=C2=A0 while MSSQL operates in a pessimistic locking mode by=
 default.=C2=A0=C2=A0<br><br>PostgreSQL can not read rows/transactions that=
=C2=A0have NOT been committed, this is possible in MSSQL=C2=A0 with=C2=A0 &=
quot;TRANSACTION ISOLATION LEVEL READ UNCOMMITTED&quot;=C2=A0 PostgreSQL wi=
ll ignore that command....<br><br>Postgresql operates in Implicit Transacti=
on mode means every command is treated as a separate=C2=A0transaction unles=
s it sees a BEGIN.=C2=A0 While MSSQL does not operate that way=C2=A0 it exp=
ects to see a BEGIN.=C2=A0 MSSQL can automatically add the BEGIN using the=
=C2=A0SET IMPLICIT_TRANSACTIONS ON =C2=A0<br><br>There are a bunch of gotch=
as like this that are not found during testing unless you are looking for t=
hem..<br><br>Thank you<br>Justin<br><br><br><br><br><br>=C2=A0<br><br>=C2=
=A0</div></div></div>

--0000000000001c09a0063cd2b2c1--