Skip to content
IntoSQL home
Convert a file

Convert PostgreSQL to SQL Server

Drop a plain dump from pg_dump or pg_dumpall, as .sql or .sql.gz, and download a script for SQL Server with the same tables, keys, indexes and data. Every value SQL Server can't hold as it is, and every object it doesn't carry, is listed in the report.

PostgreSQL to SQL Server migration guidePostgreSQL to SQL Server data types
Your file
sqlshop_2024_postgres.sqlSample file, 6.2 kB
Your database

18 rows converted, 2 fixes, none rejected

Show

Tables in shop_2024_postgres.sqlPostgreSQL 18.6 dump · written by pg_dump · utf8mb4

  • customers8 rows1
    8 columns
    idint not null
    namenvarchar(max) not null
    emailnvarchar(450) not null
    countrynvarchar(2) not null
    is_activebit not null
    tagsnvarchar(max) not null
    signed_update
    created_atdatetimeoffset(6) not null
  • orders10 rows1
    10 columns
    idint not null
    customer_idint not null
    statusnvarchar(9) not null
    totaldecimal(10,2) not null
    itemssmallint not null
    ordered_atdatetime2(6) not null
    shipped_atdatetime2(6)
    notenvarchar(max)
    detailsnvarchar(max)
    deliverynvarchar(100)

Not converted

  • View paid_orders

Each database writes these in its own language. Rewrite them for SQL Server.

-- Written by IntoSQL from a PostgreSQL dump. Load it with: sqlcmd -b -f 65001 -d <database> -i <file>
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO

CREATE TABLE [dbo].[customers] (
    [id] int IDENTITY(1001, 1) NOT NULL,
    [name] nvarchar(max) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [email] nvarchar(450) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [country] nvarchar(2) COLLATE Latin1_General_100_BIN2 DEFAULT N'US' NOT NULL,
    [is_active] bit DEFAULT 1 NOT NULL,
    [tags] nvarchar(max) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [signed_up] date NULL,
    [created_at] datetimeoffset(6) DEFAULT SYSDATETIMEOFFSET() NOT NULL,
    CONSTRAINT [PK_customers] PRIMARY KEY ([id])
);
GO

CREATE TABLE [dbo].[orders] (
    [id] int IDENTITY(5001, 1) NOT NULL,
    [customer_id] int NOT NULL,
    [status] nvarchar(9) COLLATE Latin1_General_100_BIN2 DEFAULT N'pending' NOT NULL,
    [total] decimal(10,2) NOT NULL,
    [items] smallint DEFAULT 1 NOT NULL,
    [ordered_at] datetime2(6) NOT NULL,
    [shipped_at] datetime2(6) NULL,
    [note] nvarchar(max) COLLATE Latin1_General_100_BIN2 NULL,
    [details] nvarchar(max) COLLATE Latin1_General_100_BIN2 NULL,
    [delivery] nvarchar(100) COLLATE Latin1_General_100_BIN2 NULL,
    CONSTRAINT [CK_orders_status] CHECK ([status] IN (N'pending', N'paid', N'shipped', N'cancelled')),
    CONSTRAINT [orders_total_check] CHECK ([total] >= (0)),
    CONSTRAINT [PK_orders] PRIMARY KEY ([id])
);
GO

SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [tags], [signed_up], [created_at]) VALUES
(1,N'Ana Martínez',N'ana@example.com',N'MX',1,N'["vip"]','2023-01-15','2023-01-15 10:24:00+00:00'),
(2,N'Zoë Müller',N'zoe@example.de',N'DE',1,N'[]','2023-02-03','2023-02-03 08:11:42+00:00'),
(3,N'José Peña',N'jose@example.es',N'ES',0,N'["wholesale","late payer"]','2023-03-21','2023-03-21 19:05:17+00:00'),
(4,N'李雷',N'lilei@example.cn',N'CN',1,N'[]','2023-05-09','2023-05-09 02:40:00+00:00'),
(5,N'Noah O''Brien',N'noah@example.ie',N'IE',1,N'[]',NULL,'2023-06-30 14:00:00+00:00'),
(6,N'Amélie Laurent',N'amelie@example.fr',N'FR',1,N'["vip"]','2024-01-02','2024-01-02 09:30:00+00:00'),
(7,N'Ravi Patel',N'ravi@example.in',N'IN',0,N'[]','2024-02-29','2024-02-29 23:59:59+00:00'),
(8,N'Sofia Rossi 🌻',N'sofia@example.it',N'IT',1,N'[]','2024-04-12','2024-04-12 12:12:12+00:00');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [details], [delivery]) VALUES
(1,1,N'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,N'{"gift": false}',N'1 day 06:30:00'),
(2,2,N'paid',54.00,1,'2024-03-02 11:15:00',NULL,N'Gift wrap 🎁',N'{"gift": true, "wrap": "red"}',NULL),
(3,1,N'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,N'Customer changed their mind',NULL,NULL),
(4,4,N'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00',N'请小心轻放',N'{"fragile": true}',N'2 days 05:50:00'),
(5,5,N'pending',45.75,2,'2024-03-08 17:20:00',NULL,N'Leave at the door; ring twice',NULL,NULL),
(6,6,N'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00',N'Fragile: "glass"',N'{"fragile": true}',N'20:00:00'),
(7,8,N'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,NULL,NULL),
(8,2,N'shipped',19.99,1,'2024-03-12 08:00:00',NULL,N'Shipped date set to infinity by an old import',NULL,NULL),
(9,3,N'shipped',64.00,2,'2024-03-14 15:30:00','2024-03-15 10:00:00',N'Two parcels',N'{"parcels": [1, 2]}',N'18:30:00'),
(10,7,N'paid',1499.00,1,'2024-03-15 21:05:00',NULL,N'C:\Users\ravi\invoice.pdf',NULL,NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO

-- Indexes, foreign keys, counters and triggers, after the rows.
CREATE UNIQUE INDEX [customers_email_key] ON [dbo].[customers] ([email]);
CREATE INDEX [idx_orders_customer] ON [dbo].[orders] ([customer_id]);
CREATE INDEX [idx_orders_status_date] ON [dbo].[orders] ([status], [ordered_at]);
ALTER TABLE [dbo].[orders] WITH NOCHECK ADD CONSTRAINT [orders_customer_id_fkey] FOREIGN KEY ([customer_id]) REFERENCES [dbo].[customers] ([id]);
BEGIN TRY ALTER TABLE [dbo].[orders] WITH CHECK CHECK CONSTRAINT [orders_customer_id_fkey]; END TRY BEGIN CATCH PRINT N'Foreign key ' + N'[orders_customer_id_fkey]' + N' has rows without a parent: it stays unchecked, and new rows are checked.'; END CATCH;
DECLARE @next decimal(38,0) = (SELECT MAX([id]) FROM [dbo].[customers]); IF @next IS NOT NULL BEGIN IF @next < 1000 SET @next = 1000; DBCC CHECKIDENT (N'[dbo].[customers]', RESEED, @next) WITH NO_INFOMSGS; END;
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'People who bought at least once', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'customers';
DECLARE @next decimal(38,0) = (SELECT MAX([id]) FROM [dbo].[orders]); IF @next IS NOT NULL BEGIN IF @next < 5000 SET @next = 5000; DBCC CHECKIDENT (N'[dbo].[orders]', RESEED, @next) WITH NO_INFOMSGS; END;
GO
GO

Fixes

  1. "tags" of customers is text[], which SQL Server doesn't have, so it is nvarchar(max) with the same values.
  2. "delivery" of orders is interval, which SQL Server doesn't have, so it is nvarchar(100) with the same values.

Needs a look

  • 1 view, trigger, routine or event isn't converted; the report lists each one. Recreate them by hand in SQL Server.
  • The default of "tags" in customers can't be written for SQL Server (a value its type can't hold), so the column has no default. Add the default by hand if new rows need it.
  • The index idx_orders_details on orders uses an expression that can't be written for SQL Server (an index on an expression), so it isn't created. Create the index by hand if queries need it.
  • 1 value in "shipped_at" of orders is a date SQL Server can't store, such as infinity, so it is NULL. SQL Server stores years 1 to 9999 without infinity; replace those dates in the source if they matter.

Your file is converted on this computer and never uploaded.

Other samples:

What changes from PostgreSQL to SQL Server

Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.

  • boolean

    bit

    bit holds true as 1 and false as 0.

  • varchar(n), char(n), text

    nvarchar(n); text as nvarchar(max), or nvarchar(450) when a key uses it and every value fits

    nvarchar holds every character. SQL Server can't index nvarchar(max), so text in a key is sized to fit a key.

  • text comparison

    exact (Latin1_General_100_BIN2); citext ignoring case (Latin1_General_100_CI_AS_SC)

    SQL Server's usual collation ignores case, and PostgreSQL's text doesn't, so each column says how it compares and unique keys keep the same rows apart.

  • numeric(p,s) past 38 digits; numeric without a precision

    nvarchar; decimal(p,s) fitted to the values when they fit 38 digits

    SQL Server's decimal holds 38 digits. A numeric without a precision gets the narrowest decimal its values fit; wider numbers are kept as text so every digit stays exact.

    Report line: decimal_as_text

  • money

    decimal(19,2)

    SQL Server's money is smaller than PostgreSQL's and has four decimals; decimal(19,2) holds every amount, without the currency symbol the server's locale added.

    Report line: type_replaced

  • interval

    nvarchar(100) with PostgreSQL's text

    SQL Server has no interval type; the text keeps every part.

    Report line: type_replaced

  • arrays (integer[], text[]...)

    nvarchar(max) with a JSON array

    SQL Server has no arrays. A JSON array keeps every item and NULL, and OPENJSON reads it.

    Report line: type_replaced

  • ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension types

    nvarchar(max) with PostgreSQL's text

    SQL Server has no equivalent type, so each value keeps the text PostgreSQL wrote.

    Report line: type_replaced

  • json, jsonb

    nvarchar(max)

    SQL Server's JSON functions read JSON from text.

  • timestamp

    datetime2

    A timestamp without time zone is a wall-clock reading, like datetime2.

  • timestamp with time zone

    datetimeoffset in UTC

    datetimeoffset keeps the moment; each value is written in UTC, the same instant whatever offset the dump wrote.

  • time with 24:00:00 in it

    nvarchar(17)

    SQL Server's time ends at 23:59:59.9999999, so a column holding 24:00:00 keeps its text.

    Report line: time_as_interval

  • infinity, -infinity and BC dates

    NULL

    SQL Server stores years 1 to 9999 without infinity. Those values become NULL and the report counts them.

    Report line: date_out_of_range

  • NaN and Infinity in numeric, real and double precision

    NULL

    SQL Server's numbers have no NaN or Infinity, so those values become NULL and the report counts them.

    Report line: invalid_value_null

  • NOT NULL columns with values that become NULL

    NOT NULL dropped

    A column whose values had to become NULL can't keep NOT NULL, or the load would stop.

    Report line: not_null_relaxed

  • enum types

    nvarchar with a CHECK of its labels

    Text with a check keeps the same values.

  • enum columns holding values outside their labels

    nvarchar without a check

    Values the labels don't name would fail the check, so the check isn't created.

    Report line: enum_check_skipped

  • uuid, inet, cidr, macaddr

    uniqueidentifier; addresses as nvarchar

    uniqueidentifier is SQL Server's UUID; addresses are kept as text.

  • bytea

    varbinary(max)

    Bytes stay bytes.

  • bit(n), bit varying

    bit for one bit; bigint up to 63 bits; text digits beyond

    A single bit is a yes or no; longer bit strings are stored as the number their bits make.

  • oid, xid, cid

    bigint with a CHECK that it isn't negative

    bigint holds every value.

  • serial, identity

    IDENTITY, continuing after the highest id

    The copied ids load with IDENTITY_INSERT, and the counter continues after the highest one or the sequence's next value, whichever is higher.

  • a second serial or identity column in a table

    a plain integer

    SQL Server counts one IDENTITY column per table; any other keeps its values without a counter.

    Report line: counter_dropped

  • sequences not owned by a column

    CREATE SEQUENCE ... AS bigint, at its next value

    SQL Server has sequences with the same options; each one continues where the dump left it.

  • generated columns

    computed columns, PERSISTED when the source stores them

    SQL Server computes them the same two ways.

  • generated columns with functions SQL Server lacks

    a plain column with the values

    A formula that can't be written for SQL Server leaves a plain column with the dump's values and a report line.

    Report line: generated_not_translated

  • defaults with functions SQL Server lacks, and defaults from a sequence

    no default

    A default that can't be written for SQL Server is left out and reported.

    Report line: default_not_translated

  • CHECK constraints

    translated, inline in CREATE TABLE

    Inline checks are tested as each row loads.

  • CHECK constraints with functions SQL Server lacks

    not created

    A check that can't be written for SQL Server is left out and reported.

    Report line: check_skipped

  • CHECK ... NOT VALID

    not created

    PostgreSQL never checked the rows that were there before, so they may break it.

    Report line: check_not_valid

  • gin, gist, brin and hash indexes, partial and expression indexes, and indexes on text that doesn't fit a key

    not created

    SQL Server indexes columns, not expressions, and not nvarchar(max).

    Report line: index_skipped

  • foreign keys

    after the data WITH NOCHECK, then checked with a warning on orphans

    The key is created after the rows and checked last; rows without a parent leave it unchecked with a warning instead of stopping the load.

  • ON DELETE or ON UPDATE that cascades in a cycle or along a second path

    NO ACTION

    SQL Server refuses cascades that could reach a table twice; PostgreSQL allows them.

    Report line: foreign_key_action_dropped

  • foreign keys to tables the dump doesn't create

    not created

    A key can't name a table that isn't there.

    Report line: foreign_key_skipped

  • partitioned tables

    one table with every partition's rows

    The rows are the same; partition the table again in SQL Server if it needs it.

    Report line: partitions_dropped

  • views, functions, procedures, triggers, rules, policies, extensions and types

    listed in the report

    Their code is written in PostgreSQL's language, so they are listed for you to recreate.

    Report line: not_converted_objects

  • dumps without CREATE TABLE (--data-only)

    types read from the values

    A data-only dump has only rows, so the types come from the values.

    Report line: no_schema

  • expressions in INSERT values

    NULL

    A converter that doesn't run a database can't evaluate them.

    Report line: expression_value

  • types IntoSQL doesn't know

    nvarchar(max)

    Text holds any value; change the type after loading.

    Report line: unknown_type

  • text that isn't valid in its encoding

    the bad bytes as the replacement character

    nvarchar holds Unicode text.

    Report line: invalid_utf8_replaced

  • text decoded twice (ñ)

    copied as it is

    The dump holds exactly these characters, so they are copied; the report points them out.

    Report line: mojibake_suspected

In PostgreSQLIn SQL ServerReport line
booleanbitbit holds true as 1 and false as 0.
varchar(n), char(n), textnvarchar(n); text as nvarchar(max), or nvarchar(450) when a key uses it and every value fitsnvarchar holds every character. SQL Server can't index nvarchar(max), so text in a key is sized to fit a key.
text comparisonexact (Latin1_General_100_BIN2); citext ignoring case (Latin1_General_100_CI_AS_SC)SQL Server's usual collation ignores case, and PostgreSQL's text doesn't, so each column says how it compares and unique keys keep the same rows apart.
numeric(p,s) past 38 digits; numeric without a precisionnvarchar; decimal(p,s) fitted to the values when they fit 38 digitsSQL Server's decimal holds 38 digits. A numeric without a precision gets the narrowest decimal its values fit; wider numbers are kept as text so every digit stays exact.decimal_as_text
moneydecimal(19,2)SQL Server's money is smaller than PostgreSQL's and has four decimals; decimal(19,2) holds every amount, without the currency symbol the server's locale added.type_replaced
intervalnvarchar(100) with PostgreSQL's textSQL Server has no interval type; the text keeps every part.type_replaced
arrays (integer[], text[]...)nvarchar(max) with a JSON arraySQL Server has no arrays. A JSON array keeps every item and NULL, and OPENJSON reads it.type_replaced
ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension typesnvarchar(max) with PostgreSQL's textSQL Server has no equivalent type, so each value keeps the text PostgreSQL wrote.type_replaced
json, jsonbnvarchar(max)SQL Server's JSON functions read JSON from text.
timestampdatetime2A timestamp without time zone is a wall-clock reading, like datetime2.
timestamp with time zonedatetimeoffset in UTCdatetimeoffset keeps the moment; each value is written in UTC, the same instant whatever offset the dump wrote.
time with 24:00:00 in itnvarchar(17)SQL Server's time ends at 23:59:59.9999999, so a column holding 24:00:00 keeps its text.time_as_interval
infinity, -infinity and BC datesNULLSQL Server stores years 1 to 9999 without infinity. Those values become NULL and the report counts them.date_out_of_range
NaN and Infinity in numeric, real and double precisionNULLSQL Server's numbers have no NaN or Infinity, so those values become NULL and the report counts them.invalid_value_null
NOT NULL columns with values that become NULLNOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop.not_null_relaxed
enum typesnvarchar with a CHECK of its labelsText with a check keeps the same values.
enum columns holding values outside their labelsnvarchar without a checkValues the labels don't name would fail the check, so the check isn't created.enum_check_skipped
uuid, inet, cidr, macaddruniqueidentifier; addresses as nvarcharuniqueidentifier is SQL Server's UUID; addresses are kept as text.
byteavarbinary(max)Bytes stay bytes.
bit(n), bit varyingbit for one bit; bigint up to 63 bits; text digits beyondA single bit is a yes or no; longer bit strings are stored as the number their bits make.
oid, xid, cidbigint with a CHECK that it isn't negativebigint holds every value.
serial, identityIDENTITY, continuing after the highest idThe copied ids load with IDENTITY_INSERT, and the counter continues after the highest one or the sequence's next value, whichever is higher.
a second serial or identity column in a tablea plain integerSQL Server counts one IDENTITY column per table; any other keeps its values without a counter.counter_dropped
sequences not owned by a columnCREATE SEQUENCE ... AS bigint, at its next valueSQL Server has sequences with the same options; each one continues where the dump left it.
generated columnscomputed columns, PERSISTED when the source stores themSQL Server computes them the same two ways.
generated columns with functions SQL Server lacksa plain column with the valuesA formula that can't be written for SQL Server leaves a plain column with the dump's values and a report line.generated_not_translated
defaults with functions SQL Server lacks, and defaults from a sequenceno defaultA default that can't be written for SQL Server is left out and reported.default_not_translated
CHECK constraintstranslated, inline in CREATE TABLEInline checks are tested as each row loads.
CHECK constraints with functions SQL Server lacksnot createdA check that can't be written for SQL Server is left out and reported.check_skipped
CHECK ... NOT VALIDnot createdPostgreSQL never checked the rows that were there before, so they may break it.check_not_valid
gin, gist, brin and hash indexes, partial and expression indexes, and indexes on text that doesn't fit a keynot createdSQL Server indexes columns, not expressions, and not nvarchar(max).index_skipped
foreign keysafter the data WITH NOCHECK, then checked with a warning on orphansThe key is created after the rows and checked last; rows without a parent leave it unchecked with a warning instead of stopping the load.
ON DELETE or ON UPDATE that cascades in a cycle or along a second pathNO ACTIONSQL Server refuses cascades that could reach a table twice; PostgreSQL allows them.foreign_key_action_dropped
foreign keys to tables the dump doesn't createnot createdA key can't name a table that isn't there.foreign_key_skipped
partitioned tablesone table with every partition's rowsThe rows are the same; partition the table again in SQL Server if it needs it.partitions_dropped
views, functions, procedures, triggers, rules, policies, extensions and typeslisted in the reportTheir code is written in PostgreSQL's language, so they are listed for you to recreate.not_converted_objects
dumps without CREATE TABLE (--data-only)types read from the valuesA data-only dump has only rows, so the types come from the values.no_schema
expressions in INSERT valuesNULLA converter that doesn't run a database can't evaluate them.expression_value
types IntoSQL doesn't knownvarchar(max)Text holds any value; change the type after loading.unknown_type
text that isn't valid in its encodingthe bad bytes as the replacement characternvarchar holds Unicode text.invalid_utf8_replaced
text decoded twice (ñ)copied as it isThe dump holds exactly these characters, so they are copied; the report points them out.mojibake_suspected

Load the script into SQL Server

The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.

sqlcmd -b -f 65001 -S localhost -d mydb -i postgresql.sql_server.sql

Foreign keys are added WITH NOCHECK and then checked. If a row points to a row that doesn't exist, the script says which constraint and leaves it unchecked, instead of failing half way.

For a large dump, run sqlcmd on the same machine as the server. Each batch of up to 1000 rows commits on its own, so the transaction log stays small.

Dump PostgreSQL for the best result

The converter reads what the dump holds. These options keep more of your data in it:

  • --format=plain, pg_dump's default, writes SQL. A custom or directory archive (-Fc, -Fd) becomes SQL with pg_restore -f dump.sql, without a database server.
  • --no-owner --no-privileges leave out roles and grants, which the converter skips anyway, so the dump is smaller.
  • pg_dumpall dumps every database in one file; each one's tables are named after it, so nothing collides.

What isn't converted

Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for SQL Server.

Questions about PostgreSQL to SQL Server

Not answered here? Write to us from the contact page and a person replies. Contact page

Is my dump uploaded?

No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.

How large can the dump be?

The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.

Does it read pg_dump -Fc archives?

Not directly: a custom archive is a binary format. pg_restore -f dump.sql dump.backup turns it into the plain SQL the converter reads, without connecting to a server. The converter says so when it gets an archive.

Which PostgreSQL versions can the dump come from?

Dumps written by pg_dump and pg_dumpall from PostgreSQL 10 on, with COPY or INSERT rows. Every test dump comes from PostgreSQL 13 and 18 and is compared with its source cell by cell.

Are serial and identity values kept?

Yes. Each table's serial or identity column becomes an IDENTITY column, every row keeps its id, and the next id continues after the highest one. SQL Server counts one column per table, so any other keeps its values without a counter.

What happens to arrays, enums and other PostgreSQL types?

Arrays become JSON arrays, booleans bit, uuid uniqueidentifier, and timestamps with a time zone datetimeoffset. Text compares exactly, as in PostgreSQL, and citext ignoring case. Ranges, geometric types and composite types keep PostgreSQL's text.

What about functions, triggers and views?

They are listed in the report by name and not converted, because each database writes them in its own language.

Which SQL Server versions does the script load on?

SQL Server 2017 to 2025. The same script loads on each of them, stopping at the first error if anything goes wrong.