Skip to content
IntoSQL home
Convert a file

Convert SQL Server to PostgreSQL

Drop the script SSMS's Generate Scripts writes with schema and data, as .sql or .sql.gz, UTF-16 or UTF-8, and download a script for PostgreSQL with the same tables, keys, indexes and data. Every value PostgreSQL can't hold as it is, and every object it doesn't carry, is listed in the report.

SQL Server to PostgreSQL migration guideSQL Server to PostgreSQL data types
Your file
sqlshop_2024_sqlserver.sqlSample file, 11 kB
Your database

18 rows converted, 3 fixes, none rejected

Show

Tables in shop_2024_sqlserver.sqlSQL Server dump · written by SQL Server Management Objects · utf-8

  • orders10 rows2
    11 columns
    idinteger not null
    customer_idinteger not null
    statusvarchar(10) not null
    totalnumeric(19,4) not null
    itemssmallint not null
    ordered_attimestamp(0) not null
    shipped_attimestamp(6)
    notetext
    detailstext
    order_refuuid not null
    total_with_taxnumeric(12,2)
  • customers8 rows1
    8 columns
    idinteger not null
    namevarchar(100) not null
    emailvarchar(200) not null
    countryvarchar(2) not null
    is_activeboolean not null
    signed_update
    created_attimestamptz(0) not null
    row_versionbytea

Not converted

  • View paid_orders
  • Trigger orders_no_negative

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

-- Written by IntoSQL from a SQL Server dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';

CREATE TABLE orders (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id integer NOT NULL,
    status varchar(10) DEFAULT 'pending' NOT NULL,
    total numeric(19,4) NOT NULL,
    items smallint DEFAULT 1 NOT NULL,
    ordered_at timestamp(0) NOT NULL,
    shipped_at timestamp(6),
    note text,
    details text,
    order_ref uuid DEFAULT (gen_random_uuid()::text) NOT NULL,
    total_with_tax numeric(12,2) GENERATED ALWAYS AS ((cast(total * (1.16) AS numeric(12,2)))::numeric(12,2)) STORED,
    CONSTRAINT orders_items_check CHECK (items >= 0),
    CONSTRAINT orders_check2 CHECK (status = 'cancelled' OR status = 'shipped' OR status = 'paid' OR status = 'pending'),
    CONSTRAINT orders_check3 CHECK (total >= (0))
);

CREATE TABLE customers (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name varchar(100) NOT NULL,
    email varchar(200) NOT NULL,
    country varchar(2) DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    signed_up date,
    created_at timestamptz(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    row_version bytea
);

COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1	Ana Martínez	ana@example.com	MX	t	2023-01-15	2023-01-15 10:24:00.000000+00:00
2	Zoë Müller	zoe@example.de	DE	t	2023-02-03	2023-02-03 08:11:42.000000+00:00
3	José Peña	jose@example.es	ES	f	2023-03-21	2023-03-21 19:05:17.000000+00:00
4	李雷	lilei@example.cn	CN	t	2023-05-09	2023-05-09 02:40:00.000000+00:00
5	Noah O'Brien	noah@example.ie	IE	t	\N	2023-06-30 14:00:00.000000+00:00
6	Amélie Laurent	amelie@example.fr	FR	t	2024-01-02	2024-01-02 09:30:00.000000+00:00
7	Ravi Patel	ravi@example.in	IN	f	2024-02-29	2024-02-29 23:59:59.000000+00:00
8	Sofia Rossi 🌻	sofia@example.it	IT	t	2024-04-12	2024-04-12 12:12:12.000000+00:00
\.

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) FROM stdin;
1	1	shipped	129.9000	3	2024-03-01T10:00:00.000000	2024-03-02T16:30:00.123456	\N	{"gift": false}	0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f
2	2	paid	54.0000	1	2024-03-02T11:15:00.000000	\N	Gift wrap 🎁	{"gift": true, "wrap": "red"}	1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70
3	1	cancelled	12.5000	1	2024-03-03T09:45:00.000000	\N	Customer changed their mind	\N	2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081
4	4	shipped	980.0000	12	2024-03-05T02:10:00.000000	2024-03-07T08:00:00.000000	请小心轻放	{"fragile": true}	3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192
5	5	pending	45.7500	2	2024-03-08T17:20:00.000000	\N	Leave at the door; ring twice	\N	4f1e806c-7d5b-4c2e-9a99-5e6f708192a3
6	6	shipped	310.4000	5	2024-03-10T13:00:00.000000	2024-03-11T09:00:00.000000	Fragile: "glass"	{"fragile": true}	50f2917d-8e6c-4d3f-8baa-6f708192a3b4
7	8	paid	76.2000	2	2024-03-11T18:45:00.000000	\N	\N	\N	6103a28e-9f7d-4e40-9cbb-708192a3b4c5
8	2	shipped	19.9900	1	2024-03-12T08:00:00.000000	9999-12-31T23:59:59.999999	Shipped date set to the maximum by an old import	\N	7214b39f-a08e-4f51-8dcc-8192a3b4c5d6
9	3	shipped	64.0000	2	2024-03-14T15:30:00.000000	2024-03-15T10:00:00.000000	Two parcels	{"parcels": [1, 2]}	8325c4a0-b19f-4062-9edd-92a3b4c5d6e7
10	7	paid	1499.0000	1	2024-03-15T21:05:00.000000	\N	C:\\Users\\ravi\\invoice.pdf	\N	9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_email ON customers (email);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_email UNIQUE USING INDEX customers_email;
ALTER TABLE orders ADD CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_fkey;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 1), 2147483647)::bigint, false);
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1), 2147483647)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';

Fixes

  1. "items" of orders is unsigned, so it became smallint with a check that it isn't negative.
  2. "shipped_at" of orders is datetime2(7), with more digits of a second than PostgreSQL keeps, so it is timestamp(6) and the last digits are cut.
  3. "row_version" of customers is a row version SQL Server sets by itself; the dump has no values for it, so it is bytea and empty.

Needs a look

  • 6 text columns compare ignoring case in the source; PostgreSQL compares exactly, so unique keys and searches may behave differently. Use a case-insensitive collation or column type on the columns that need it.
  • 2 views, triggers, routines or events aren't converted; the report lists each one. Recreate them by hand in PostgreSQL.
  • The check check1 on orders can't be written for PostgreSQL (function isjson), so it isn't created. Write the check by hand if it matters.
  • The index idx_orders_pending on orders uses an expression that can't be written for PostgreSQL (filtered index), so it isn't created. Create the index by hand if queries need it.

Your file is converted on this computer and never uploaded.

Other samples:

What changes from SQL Server to PostgreSQL

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

  • bit

    boolean

    bit holds 1 and 0, which are PostgreSQL's true and false.

  • tinyint

    smallint with a CHECK that it isn't negative

    SQL Server's tinyint runs from 0 to 255; PostgreSQL's smallest integer is signed, so a check keeps its range.

    Report line: unsigned_widened

  • char(n), varchar(n), nchar(n), nvarchar(n), varchar(max), text

    varchar(n) and text

    PostgreSQL text holds every character, so varchar and nvarchar are the same type.

  • decimal(p,s), money, smallmoney

    numeric(p,s); money as numeric(19,4)

    Every digit is kept, money with its four decimals.

  • datetime, datetime2, smalldatetime

    timestamp(p)

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

  • datetime2(7), time(7) and datetimeoffset(7)

    six digits of a second, the seventh cut

    PostgreSQL keeps microseconds; the seventh digit (100 nanoseconds) is cut, never rounded, so no value moves to the next second or day.

    Report line: fraction_cut

  • datetimeoffset

    timestamptz, the same instants

    timestamptz keeps the instant; PostgreSQL shows it in the session's time zone instead of the offset each value had.

  • time

    time(p)

    The same times of day.

  • json (SQL Server 2025)

    jsonb

    The same JSON values.

  • uniqueidentifier

    uuid

    The same identifiers, written in lowercase.

  • binary, varbinary, image

    bytea

    Bytes stay bytes.

  • geometry, geography

    bytea with SQL Server's own bytes

    SSMS writes spatial values in SQL Server's serialization, which is kept byte for byte.

    Report line: spatial_as_native

  • rowversion, timestamp

    bytea, empty

    SQL Server sets a row version by itself and SSMS scripts no values for it.

    Report line: rowversion_empty

  • sql_variant, hierarchyid, vector

    text

    PostgreSQL has no equivalent type; sql_variant keeps each value's text and hierarchyid its path, such as /1/2/. xml stays xml.

    Report line: type_replaced

  • collations that ignore case (_CI_)

    exact comparison

    PostgreSQL compares text exactly, so columns that ignored case in SQL Server now tell Ana and ana apart.

    Report line: case_insensitive_collation

  • IDENTITY

    GENERATED BY DEFAULT AS IDENTITY, continuing after the highest id

    The copied ids load as they are, and the counter continues after the highest one.

  • CREATE SEQUENCE

    CREATE SEQUENCE with the same options

    PostgreSQL sequences work the same way. SSMS doesn't script where a sequence has reached, so it starts where it was created.

  • computed columns

    generated columns, STORED

    PostgreSQL 13 to 17 only store generated columns; the formula is the same, with CONCAT written so NULL counts as empty as in SQL Server.

    Report line: virtual_as_stored

  • computed columns with functions PostgreSQL lacks

    a plain column

    A formula that can't be written for PostgreSQL leaves a plain column and a report line; SSMS scripts no values for computed columns.

    Report line: generated_not_translated

  • defaults with functions PostgreSQL lacks, and NEXT VALUE FOR

    no default

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

    Report line: default_not_translated

  • CHECK constraints

    translated

    The same checks, tested as each row loads.

  • CHECK constraints with functions PostgreSQL lacks

    not created

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

    Report line: check_skipped

  • CHECK constraints added WITH NOCHECK or disabled

    not created

    SQL Server doesn't apply these checks, so the rows may break them.

    Report line: check_not_enforced

  • filtered and columnstore indexes

    not created

    IntoSQL writes b-tree indexes on columns; create the others again after loading.

    Report line: index_skipped

  • foreign keys

    foreign keys, after the rows

    The rows load in any order, and the keys check them afterwards.

  • 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

  • tables on a partition scheme

    one table with every partition's rows

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

    Report line: partitions_dropped

  • temporal tables (SYSTEM_VERSIONING)

    two tables: the current rows and the history

    The table and its history table are copied as plain tables; PostgreSQL doesn't keep the history by itself.

    Report line: system_versioning_dropped

  • 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

  • views, functions, procedures, triggers, synonyms, types and partition functions

    listed in the report

    Their code is written in T-SQL, so they are listed for you to recreate.

    Report line: not_converted_objects

  • scripts without CREATE TABLE (data only)

    types read from the values

    A data-only script 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

    text

    Text holds any value; change the type after loading.

    Report line: unknown_type

  • dates that aren't valid

    NULL

    A value that isn't a date becomes NULL and the report counts it.

    Report line: date_out_of_range

  • NUL characters in text

    removed

    PostgreSQL text can't hold a NUL character. It is removed and the report counts the values.

    Report line: nul_removed

  • text that isn't valid in its encoding

    the bad bytes as the replacement character

    PostgreSQL text must be valid UTF-8.

    Report line: invalid_utf8_replaced

  • text decoded twice (ñ)

    copied as it is

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

    Report line: mojibake_suspected

In SQL ServerIn PostgreSQLReport line
bitbooleanbit holds 1 and 0, which are PostgreSQL's true and false.
tinyintsmallint with a CHECK that it isn't negativeSQL Server's tinyint runs from 0 to 255; PostgreSQL's smallest integer is signed, so a check keeps its range.unsigned_widened
char(n), varchar(n), nchar(n), nvarchar(n), varchar(max), textvarchar(n) and textPostgreSQL text holds every character, so varchar and nvarchar are the same type.
decimal(p,s), money, smallmoneynumeric(p,s); money as numeric(19,4)Every digit is kept, money with its four decimals.
datetime, datetime2, smalldatetimetimestamp(p)A datetime is a wall-clock reading, like a timestamp without time zone.
datetime2(7), time(7) and datetimeoffset(7)six digits of a second, the seventh cutPostgreSQL keeps microseconds; the seventh digit (100 nanoseconds) is cut, never rounded, so no value moves to the next second or day.fraction_cut
datetimeoffsettimestamptz, the same instantstimestamptz keeps the instant; PostgreSQL shows it in the session's time zone instead of the offset each value had.
timetime(p)The same times of day.
json (SQL Server 2025)jsonbThe same JSON values.
uniqueidentifieruuidThe same identifiers, written in lowercase.
binary, varbinary, imagebyteaBytes stay bytes.
geometry, geographybytea with SQL Server's own bytesSSMS writes spatial values in SQL Server's serialization, which is kept byte for byte.spatial_as_native
rowversion, timestampbytea, emptySQL Server sets a row version by itself and SSMS scripts no values for it.rowversion_empty
sql_variant, hierarchyid, vectortextPostgreSQL has no equivalent type; sql_variant keeps each value's text and hierarchyid its path, such as /1/2/. xml stays xml.type_replaced
collations that ignore case (_CI_)exact comparisonPostgreSQL compares text exactly, so columns that ignored case in SQL Server now tell Ana and ana apart.case_insensitive_collation
IDENTITYGENERATED BY DEFAULT AS IDENTITY, continuing after the highest idThe copied ids load as they are, and the counter continues after the highest one.
CREATE SEQUENCECREATE SEQUENCE with the same optionsPostgreSQL sequences work the same way. SSMS doesn't script where a sequence has reached, so it starts where it was created.
computed columnsgenerated columns, STOREDPostgreSQL 13 to 17 only store generated columns; the formula is the same, with CONCAT written so NULL counts as empty as in SQL Server.virtual_as_stored
computed columns with functions PostgreSQL lacksa plain columnA formula that can't be written for PostgreSQL leaves a plain column and a report line; SSMS scripts no values for computed columns.generated_not_translated
defaults with functions PostgreSQL lacks, and NEXT VALUE FORno defaultA default that can't be written for PostgreSQL is left out and reported.default_not_translated
CHECK constraintstranslatedThe same checks, tested as each row loads.
CHECK constraints with functions PostgreSQL lacksnot createdA check that can't be written for PostgreSQL is left out and reported.check_skipped
CHECK constraints added WITH NOCHECK or disablednot createdSQL Server doesn't apply these checks, so the rows may break them.check_not_enforced
filtered and columnstore indexesnot createdIntoSQL writes b-tree indexes on columns; create the others again after loading.index_skipped
foreign keysforeign keys, after the rowsThe rows load in any order, and the keys check them afterwards.
foreign keys to tables the dump doesn't createnot createdA key can't name a table that isn't there.foreign_key_skipped
tables on a partition schemeone table with every partition's rowsThe rows are the same; partition the table again in PostgreSQL if it needs it.partitions_dropped
temporal tables (SYSTEM_VERSIONING)two tables: the current rows and the historyThe table and its history table are copied as plain tables; PostgreSQL doesn't keep the history by itself.system_versioning_dropped
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
views, functions, procedures, triggers, synonyms, types and partition functionslisted in the reportTheir code is written in T-SQL, so they are listed for you to recreate.not_converted_objects
scripts without CREATE TABLE (data only)types read from the valuesA data-only script 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 knowtextText holds any value; change the type after loading.unknown_type
dates that aren't validNULLA value that isn't a date becomes NULL and the report counts it.date_out_of_range
NUL characters in textremovedPostgreSQL text can't hold a NUL character. It is removed and the report counts the values.nul_removed
text that isn't valid in its encodingthe bad bytes as the replacement characterPostgreSQL text must be valid UTF-8.invalid_utf8_replaced
text decoded twice (ñ)copied as it isThe script holds exactly these characters, so they are copied; the report points them out.mojibake_suspected

Load the script into PostgreSQL

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.

psql -v ON_ERROR_STOP=1 -d mydb -f sql_server.postgresql.sql

Foreign keys are added as NOT VALID and then validated. If a row points to a row that doesn't exist, the script tells you which constraint, instead of failing half way.

For a large dump, create the database first and run the script with psql from the same machine as the server.

IntoSQL Pro loads the file straight into PostgreSQL from the desktop app or the command line, without writing a script.

Dump SQL Server for the best result

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

  • In SSMS, right-click the database, Tasks, Generate Scripts, and pick the tables. dbatools, mssql-scripter and other tools built on the same library write the same script.
  • Under Advanced, set Types of data to script to Schema and data. A script of the schema alone has tables without rows.
  • Save it as Unicode, SSMS's default, or as UTF-8: both are read the same way, and gzip works too.

What isn't converted

Views, functions, procedures, triggers, synonyms, rules and security policies are written in T-SQL. They are listed in the report by name, never dropped silently, so you can rewrite them for PostgreSQL.

Questions about SQL Server to PostgreSQL

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 the UTF-16 file SSMS saves?

Yes. SSMS saves scripts as UTF-16 by default, and the converter reads UTF-16 and UTF-8 alike, gzipped or not, with GO between batches.

Which SQL Server versions can the script come from?

Scripts that SSMS, dbatools or mssql-scripter write from SQL Server 2017 on. Every test script comes from SQL Server 2017, 2022 and 2025 and is compared with its source cell by cell.

Are IDENTITY values kept?

Yes. IDENTITY columns become identity columns, every row keeps its id, and the next id continues after the highest one. Sequences are created with the same options.

What happens to money, datetimeoffset and other SQL Server types?

bit becomes boolean, money numeric(19,4), uniqueidentifier uuid, xml xml, and datetimeoffset timestamptz. datetime2 keeps six digits of a second, the seventh cut. Columns that ignored case compare exactly, and the report says which ones.

What about procedures, triggers and views?

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

Which PostgreSQL versions does the script load on?

PostgreSQL 13 to 18. The same script loads on each of them, stopping at the first error if anything goes wrong.