View a markdown version of this page

Naming rules - AWS Database Migration Service

Naming rules

The following naming rules apply when your source database is Microsoft SQL Server. DMS Schema Conversion applies your transformation rules first, and then applies the default rules for case, prefixes, uniqueness, and length to the result.

Naming by object type

The following table lists the rule that DMS Schema Conversion applies to each kind of database object. Where DMS Schema Conversion generates a name rather than deriving it from the source, the table shows the pattern.

Object typeNaming ruleExample
DatabaseNot created. Its name is merged into the schema name with the default parameter (DB_SCHEMA) in the setting Schema name template; with parameter (DB) it becomes the schema name; with parameter (SCHEMA) it is dropped. Code that references another database (OtherDB.dbo.Table) is rewritten to the merged schema name and resolves only if that database is converted into the same target database; otherwise an action item is raised.MyDatabase.dbo → mydatabase_dbo / mydatabase / dbo; OtherDB.dbo.Table → otherdb_dbo.table
SchemaDatabase and schema combined per the Schema name template setting, then lowercased.MyDatabase.dbo → mydatabase_dbo
TableSchema-qualified, lowercased.MyDatabase.dbo.UserAccounts → mydatabase_dbo.useraccounts
Temporary tableLeading # becomes t$. No schema qualifier is added.#TempResults → t$tempresults
Table variableThe @ is dropped and the declaring routine's name is appended.@Rows declared in usp_load → rows$usp_load
Table-valued function return tableThe table declared in RETURNS @name TABLE (…) is named after the function with a fixed suffix; its own name isn't used.RETURNS @Result TABLE in fn_Split → fn_split$tmptbl
ViewSchema-qualified, lowercased.MyDatabase.dbo.V_Orders → mydatabase_dbo.v_orders
IndexPrefixed IX_ and qualified with the table name when Generate unique names for indexes is on (the default), so the name is unique within the schema. The assembled name is re-truncated if needed.Idx2 on TestNonClusteredIndex → ix_testnonclusteredindex_idx2
ColumnLowercased if a plain identifier.Order_ID → order_id
ParameterLeading @ replaced by par_.@CustomerId → par_customerid
VariableLeading @ replaced by var_.@Total → var_total
Procedure, functionSchema-qualified, lowercased. Scalar, table-valued, and inline functions all become functions.dbo.usp_Load → mydatabase_dbo.usp_load
SequenceSchema-qualified, lowercased.dbo.Seq_Order → mydatabase_dbo.seq_order
User-defined table typeSchema-qualified with a table-type suffix. A plain user-defined type becomes a domain and takes no suffix.Employee2Client → employee2client$aws$t
Trigger (defined in the source)Keeps its name, lowercased. Triggers that use both inserted and deleted might be split into several triggers.trg_Products_OnUpdate → trg_products_onupdate
Trigger function (for a source trigger)The converted trigger name prefixed with fn_.trg_Products_OnUpdate → fn_trg_products_onupdate
Procedure companion for open result sets (when Create additional procedures for open datasets is on)A second procedure is generated with a fixed suffix.usp_report → usp_report$TMP
Trigger and function generated by the conversionBuilt from the table name with an event suffix: _biu for ROWVERSION and computed columns, _bi / _bu for sparse column sets, tr_hist_ for temporal tables. See the section about conversions that create additional target objects.table Orders → tr_orders_biu, fn_tr_orders_biu

Settings that affect naming

Several conversion settings change how DMS Schema Conversion generates names: Schema name template, Keep object names in the same case, Generate unique names for indexes, Keep original parameter names, Convert procedures to functions, Create stubs in separate schema, Convert unsupported built-ins to stubs, and Create additional procedures for open datasets. For their values and defaults, see SQL Server to PostgreSQL conversion settings. The following naming rules assume the default values.

Transformation rules that affect naming

Transformation rules let you override the name of selected objects. You can rename them; add, remove, or replace a prefix or suffix; or force lowercase. DMS Schema Conversion applies transformation rules before the default naming rules, so casing, uniqueness, and truncation still run afterward on the name that you specify. For the rule syntax, actions, and object locators, see Transformation rules in DMS Schema Conversion. The following naming rules assume that no transformation rules are defined.

Case sensitivity

The following describes how DMS Schema Conversion handles the case of source object names. PostgreSQL folds unquoted identifiers to lowercase, so the case that a name arrives in determines whether you can use it unquoted in the target.

Note

Plain identifiers. Most naming rules apply only to a plain identifier — a name matching ^[A-Za-z_][0-9A-Za-z$_]*$ that isn't a target reserved word. Any name containing spaces, dots, quotes, or non-ASCII characters is left exactly as it is, and is quoted in the target instead. This is the single most useful rule to remember: if a name isn't a plain identifier, its case is never changed.

Note

Case sensitivity is controlled by one setting. By default, every plain identifier is lowercased. Enabling Keep object names in the same case preserves the original case instead. Two related properties — whether the source database and the source server are themselves case sensitive — are read from your source database rather than configured, and DMS Schema Conversion doesn't require them for case preservation. Routine parameters are additionally preserved only where another parameter of the same name would otherwise collide.

Examples

SQL Server inputOutput (default)Why
sourceTableNamesourcetablenamePlain identifier → lowercased
AccessTokenaccesstokenPlain identifier → lowercased
_Conventions_Test_3_conventions_test_3Plain identifier → lowercased
_Conventions test 2"_Conventions test 2"Contains space → not plain, never lowercased
space_02" space_02"Starts with space → not plain
Б'Ёка1"Б'Ёка1"Non-ASCII → not plain, never lowercased
a'b'c"a'b'c"Contains quotes → not plain

Name length and truncation

PostgreSQL limits identifiers to 63 bytes, whereas SQL Server allows 128 characters. Names most often exceed the PostgreSQL limit because of the names that DMS Schema Conversion assembles itself — merged database and schema names, and index names qualified with their table. The limit is a property of the target engine and isn't configurable.

A name can exceed the limit only after DMS Schema Conversion rebuilds it. The SQL Server index IX_VTN_Course_CompanyID_CourseType_IsDeleted_CourseID on table VTN_Course is 53 characters, within the PostgreSQL limit, but the setting Generate unique names for indexes rebuilds the name as ix_<table>_<index>. The assembled name is longer than 63 bytes, so DMS Schema Conversion shortens it and adds a $ plus a short hash, giving ix_vtn_course_ix_vtn_course_companyid_coursetype_isdel$3d2b343f.

DMS Schema Conversion derives the hash from the object's full owner path — and, for routines, from the parameter signature as well — so two long names that share a prefix still convert to different target names instead of colliding.

Reserved words treated as non-plain identifiers

DMS Schema Conversion doesn't treat a source name that matches one of the following PostgreSQL reserved words as a plain identifier. It keeps the name exactly as spelled in your source and quotes it in the target. Because the name is quoted, you must quote it everywhere that you reference it in the target.

For a SQL Server source, a table named Order becomes "Order".

ALL, ANALYSE, ANALYZE, AND, ANY, ARRAY, AS, ASC, ASYMMETRIC, BOTH, CASE, CAST, CHECK, COLLATE, COLUMN, CONSTRAINT, CREATE, CURRENT_CATALOG, CURRENT_DATE, CURRENT_ROLE, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DAY_HOUR, DEFAULT, DEFERRABLE, DESC, DISTINCT, DO, ELSE, END, EXCEPT, EXTRACT, FALSE, FETCH, FOR, FOREIGN, FROM, GRANT, GROUP, HAVING, IN, INITIALLY, INTERSECT, INTO, LATERAL, LEADING, LEFT, LIMIT, LOCALTIME, LOCALTIMESTAMP, NOT, NULL, OFFSET, ON, ONLY, OR, ORDER, OUTER, PLACING, PRIMARY, REFERENCES, RETURNING, RIGHT, SELECT, SESSION_USER, SOME, SQLSTATE, SYMMETRIC, TABLE, THEN, TO, TRAILING, TRUE, UNION, UNIQUE, UPDATE, USER, USING, VARIADIC, WHEN, WHERE, WINDOW, WITH