Object type rules
The following table lists how DMS Schema Conversion converts SQL Server object types to PostgreSQL. You can't change these rules, except where a table names a conversion setting.
| Source object type | Converted to |
|---|---|
| Database | Not created as an object. |
| Schema, table, view, column, index, constraint, sequence, partition | Same kind of object in PostgreSQL. |
| Graph table, external table | Not converted — reported as unsupported. |
| Trigger | PostgreSQL trigger plus a generated trigger function. |
| Scalar function | Function. |
| Table-valued function | Function RETURNS TABLE. |
| Inline table-valued function | Set-returning function RETURNS TABLE. |
| Aggregate (CLR) function | Not converted. |
| Procedure | PostgreSQL procedure by default; PostgreSQL function when Convert procedures to functions is enabled. For more information, see Settings that affect procedure conversion. |
| User-defined type (alias type) | Domain. |
| User-defined table type | Composite type. |
| Synonym | Not converted — reported as unsupported. |
| XML schema collection | Not converted — reported as unsupported. |
| Partition scheme, partition function | Not created as objects — folded into the partitioned table's definition. |
| Database trigger, server trigger, linked server, endpoint, assembly, SQL Agent job / alert / operator / schedule | Not converted — reported as unsupported. |
Settings that affect procedure conversion
By default, a source procedure is converted to a PostgreSQL
PROCEDURE that is invoked with CALL; its output
parameters remain procedure parameters. When the setting Convert
procedures to functions is enabled, the procedure is converted to
a PostgreSQL FUNCTION instead: it returns void if
the procedure has no output parameters, otherwise it returns the
OUT parameters. If a SQL Server procedure returns a status value, the
converted procedure also receives an INOUT return_code parameter
that carries the return status. This also applies when you convert procedures
to functions.
For the setting values and defaults, see SQL Server to PostgreSQL conversion settings.
Conversions that create additional target objects
Some source constructs have no single PostgreSQL equivalent. To reproduce their behavior, DMS Schema Conversion generates extra objects — such as triggers, trigger functions, sequences, domains, types, views, or constraints — alongside the converted object. This section lists every such case, what is generated, and how the generated objects are named.
| Source construct | What is generated | Generated names | When |
|---|---|---|---|
TIMESTAMP /
ROWVERSION column |
Column becomes BIGINT. A trigger function and
a BEFORE INSERT OR UPDATE trigger are generated
to assign the next value and to reject explicit writes to the
column. |
fn_tr_<table>_biu,
tr_<table>_biu; uses the extension pack
sequence
aws_sqlserver_ext_data.inc_seq_rowversion |
Always |
Computed column whose converted
expression is not IMMUTABLE
(e.g. uses GETDATE(), SUSER_SNAME(),
a user function) |
A plain column is created and a trigger function plus
trigger keep it up to date and block direct modification.
Computed columns with IMMUTABLE expressions become
GENERATED ALWAYS AS (…) STORED and need no extra
objects. |
fn_tr_<table>_biu,
tr_<table>_biu (one pair per table, shared
with the ROWVERSION case) |
When the expression is not immutable |
Table with SPARSE
columns and a COLUMN_SET |
Two triggers and two trigger functions maintain the XML column set on insert and on update. | tr_<table>_bi /
fn_tr_<table>_bi and
tr_<table>_bu /
fn_tr_<table>_bu |
Always when both features are present |
| System-versioned temporal table | A history trigger and trigger function write previous row versions to the history table. | tr_hist_<table>,
fn_tr_hist_<table> |
Always |
| Trigger | Every trigger needs a trigger function in PostgreSQL. A
trigger that uses both inserted and
deleted where PostgreSQL can't provide both
transition tables is split into several triggers, and an
empty placeholder function is generated for events with no
usable transition table. |
fn_<trigger>; split triggers keep the
source name with an event suffix |
Always |
| User-defined table type | A composite type, a domain defined as an array of that type (so table-valued parameters can be passed), and a helper function that populates it. | <type>$aws$t plus its array domain and
helper function |
Always |
| User-defined (alias) type | A domain over the base type. No further objects. | <schema>.<type> |
Always |
| String column, when Use CITEXT for all string data types is enabled | Character columns and variables whose collation is not
case-sensitive become CITEXT; CREATE
EXTENSION IF NOT EXISTS citext is emitted before the
schema. Because CITEXT has no length, a
CHECK constraint on the character length is added
for every column that had one, so the source limit is
preserved. With the setting off (the default) no CITEXT is
used, whatever the source collation. |
extension citext; one check constraint per
column |
Only when the setting is enabled (off by default) |
IDENTITY(seed,
increment) column |
GENERATED ALWAYS AS IDENTITY (START WITH … INCREMENT
BY …). PostgreSQL creates the backing sequence
implicitly, so no separate object appears in the converted
schema. |
— | Always |
Built-in function with no native equivalent
(for example
TODATETIMEOFFSET(),
DATETIMEFROMPARTS(), a bit
conversion) |
The expression is rewritten to call an extension pack function. No object is generated in your schema, but the extension pack must be installed on the target. Functions that the extension pack doesn't emulate are left in place and reported as action items instead. | aws_sqlserver_ext.* |
Whenever such a function is referenced |
| Procedure that returns a status | An extra INOUT return_code int DEFAULT 0
parameter is appended to carry the SQL Server return status.
It keeps this exact name — the par_ prefix
isn't applied to it. The parameter is added in both
conversion modes, whether the procedure becomes a PostgreSQL
procedure or a function. |
parameter return_code |
When the source procedure returns a status value or sets
one in a TRY…CATCH block |
GEOGRAPHY /
GEOMETRY column |
Types map to PostGIS GEOGRAPHY /
GEOMETRY; the target must have PostGIS
installed. |
extension postgis (required, not
generated) |
Always |