From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: MSSQL to PostgreSQL Migration
Date: Thu, 12 Jan 2023 18:37:08 +0100
Message-ID: <bb68633a-28d7-2a46-f815-c64dbb2660d2@jakobs.com> (raw)
In-Reply-To: <CAKPjcsGseO8qA7fi=HN=xWeqoRbAWhXixdv26HVch_xp8R42hQ@mail.gmail.com>
References: <CANhObs2JcdUmxm_gVEpqbCe76_v18R5JAEtTrU5B5wPCx4DNOg@mail.gmail.com>
<CAAo1mbmfyHdfkNDsEs=SDjSdU4kssWi9xJvLsdL9cCgNtd5FKw@mail.gmail.com>
<CANhObs1R4LFy0DHZCf-FjOch+yUpDyP-59Zd+hDRkp=ZGfkuzg@mail.gmail.com>
<CAKPjcsGseO8qA7fi=HN=xWeqoRbAWhXixdv26HVch_xp8R42hQ@mail.gmail.com>
Am 12.01.23 um 04:21 schrieb Gurudutt Dhareshwar:
> For data you can move it using the SQL tool itself or take a BCP Out
> and then insert using the psql -d option .
Actually, bcp.exe does not do a very good job. It cannot make a
difference between an empty string and a NULL value, for instance.
If you try to export in tab-separated format
(https://www.iana.org/assignments/media-types/text/tab-separated-values),
carriage returns, newlines, tab character will all mess up the output.
And contained backslashes are not doubled as necessary for PG's copy
statement.
In case your tables aren't too large, you can export via PowerShell
keeping the differences between NULL and empty string. The way via JSON
may be a bit slow and heavy on memory, though.
### Code for Powershell
install-module sqlserver
$SqlParams = @{
ServerInstance = 'server_name'
UserName = 'user_name'
Password = 'very_secret'
Database = 'db_name'
}
$tableName = 'whatever_tablename'
(invoke-sqlcmd @SqlParams -query "Select * from $tableName" |
select-object * -excludeproperty
itemarray,table,rowerror,rowstate,haserrors |
convertto-json).replace('null', '"§n§"') | convertfrom-json | export-csv
-path "$($tableName).csv" -usequotes asneeded
### import using psql
\copy whatever_tablename from whatever_tablename.csv (format csv, header
on);
Kind Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../bb68633a-28d7-2a46-f815-c64dbb2660d2@jakobs.com/2-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: MSSQL to PostgreSQL Migration
In-Reply-To: <bb68633a-28d7-2a46-f815-c64dbb2660d2@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox