Feature or Syntax | Behavior or Difference Description |
\\ (Line Continuation Character) | Currently, line continuation characters (backslashes before newline) for strings and hexadecimal strings are not supported. For strings, the backslash followed by a newline is interpreted as a character in the string. For hexadecimal strings, the backslash followed by a newline will result in a syntax error. |
@@version | The format of the value returned by @@version is slightly different from that of the value returned by SQL Server. If your code depends on the formatting of @@version, it may not work properly. |
Aggregate Function | Partially support for aggregate functions (supports AVG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, STRING_AGG, and SUM). |
ALTER TABLE | Only adding or deleting a single column or constraint is supported. |
BACKUP Statement | Backup methods differ. In TencentDB for PostgreSQL's SQL Server Compatible Edition, backup operations can only be conducted via the cloud console. |
Blank Column Names Without Aliases | sqlcmd and psql handling columns with empty names differently: SQL Server sqlcmd returns a blank column name. PostgreSQL psql returns a system-generated column name. |
Data Type Indexes According to ICU Library Collation Rules | When the library version is changed, indexes of user-defined types dependent on the International Components for Unicode (ICU) collation library (used by the MSSQL Compatible Edition) will not become invalid. |
COLLATIONPROPERTY Function | Collation rule properties are only applicable to the supported collation rules in the MSSQL Compatible Edition BBF. |
Original Column Settings | When a column with the original setting is being created, the constraint name is ignored. To delete the original setting of a column, please use the following syntax: ALTER TABLE...ALTER COLUMN..DROP DEFAULT... . |
Constraint | In PostgreSQL, individual constraint conditions cannot be enabled or disabled. The statement will be ignored and a warning will be issued. |
Constraints Created Using DESC (Descending Order) Columns | Constraints are created using ASC (ascending order) columns. |
Constraints with the IGNORE_DUP_KEY | Creating constraints with this attribute is not supported. |
CREATE, ALTER, DROP SERVER ROLE | ALTER SERVER ROLE is only supported for sysadmin. Other syntax is not supported. In MSSQL Compatible Edition, T-SQL user experiences regarding log-ins (server principals), databases, and database users (database principals) are similar to those in SQL Server. In MSSQL Compatible Edition, only the dbo user exists in the user databases. To operate as the dbo user, the log-in name must be a member of the server-level sysadmin role (ALTER SERVER ROLE sysadmin ADD MEMBER log-in). Log-ins that are not of the sysadmin role currently can only access the master and tempdb databases as the guest user. Currently, since the MSSQL Compatible Edition supports only the dbo user in user databases, all application users must use a log-in name that is a member of the sysadmin role. You cannot create users with lower permissions, such as read-only access to certain tables. |
CREATE, ALTER LOGIN Clauses Supporting Limited Syntax | The clauses CREATE LOGIN... PASSWORD, ...DEFAULT_DATABASE, and ...DEFAULT_LANGUAGE are supported. The clause ALTER LOGIN... PASSWORD is supported, but the clause ALTER LOGIN... OLD_PASSWORD is not supported. Only log-in names that are members of the system administrator can modify passwords. |
CREATE DATABASE Case-Sensitive Collation Rules | The CREATE DATABASE statement does not support case-sensitive collation rules. |
CREATE DATABASE Keywords and Clauses | Options other than COLLATE and CONTAINMENT=NONE are not supported. The COLLATE clause can only accept the value set by babelfishpg_tsql.server_collation_name. |
CREATE SCHEMA... Supported Clauses | You can use the CREATE SCHEMA command to create an empty schema. Use other commands to create schema objects. |
CREATE, ALTER LOGIN Clauses Supporting Limited Syntax | The clauses CREATE LOGIN... PASSWORD, ...DEFAULT_DATABASE, and ...DEFAULT_LANGUAGE are supported. The clause ALTER LOGIN... PASSWORD is supported, but the clause ALTER LOGIN... OLD_PASSWORD is not supported. Only log-in names that are members of the system administrator can modify passwords. |
LOGIN Objects | All options for LOGIN objects are supported, except for the following: PASSWORD, DEFAULT_DATABASE, ENABLE, DISABLE. |
Database ID Values | The primary database and tempdb database will not be database IDs 1 and 2. |
Identifiers Exceeding 63 Characters | PostgreSQL supports up to 63 characters for identifiers. The MSSQL Compatible Edition will convert identifiers exceeding 63 characters into names containing the original name hash. |
Support for IDENTITY Columns | IDENTITY columns are supported for data types tinyint, smallint, int, bigint, numeric, and decimal. SQL Server supports up to 38 digits of precision for the data types numeric and decimal in IDENTITY columns. PostgreSQL supports up to 19 digits of precision for the data types numeric and decimal in IDENTITY columns. |
Using IGNORE_DUP_KEY in Indexes | The syntax for creating an index with IGNORE_DUP_KEY will create an index as if this attribute is omitted. |
Indexes Containing More Than 32 Columns | Indexes cannot contain more than 32 columns. The number of included index columns is counted towards the maximum in PostgreSQL, but not in SQL Server. |
Index (Clustered) | The creation of a clustered index is as if NONCLUSTERED is specified. |
Index Clause | Ignore the following clauses: FILLFACTOR, ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, PAD_INDEX, STATISTICS_NORECOMPUTE, OPTIMIZE_FOR_SEQUENTIAL_KEY, SORT_IN_TEMPDB, DROP_EXISTING, ONLINE, COMPRESSION_DELAY, MAXDOP, and DATA_COMPRESSION. |
NEWSEQUENTIALID Function | Implemented as NEWID; does not guarantee sequential behavior. When NEWSEQUENTIALID is called, PostgreSQL generates a new GUID value. |
OUTER APPLY | SQL Server's lateral joins are not supported. PostgreSQL offers SQL syntax for lateral joins, but the behavior is different. |
OUTPUT Clause Is Supported with the Following Limitations: | Simultaneous use of OUTPUT and OUTPUT INTO in the same DML query is not supported. Referring to non-target tables in the OUTPUT clause for UPDATE or DELETE operations is not allowed. OUTPUT... DELETED * , INSERTED * in the same query is not supported. |
Procedure or Function Parameter Limitations | The MSSQL Compatible Edition supports up to 100 parameters for procedures or functions. |
RESTORE Statement | PostgreSQL snapshots of the database are different from the backup files created in SQL Server. Additionally, the granularity of backup and restore between SQL Server and PostgreSQL may also differ. |
ROLLBACK: Table variables do not support transaction rollback. | If a rollback occurs in a session containing table variables, the process may be interrupted. |
ROWGUIDCOL | This clause is currently ignored. Referencing $GUIDGOL in the query leads to a syntax error. |
Support for SEQUENCE Objects | Data types tinyint, smallint, int, bigint, numeric, and decimal support SEQUENCE objects. For the data types numeric and decimal in SEQUENCE, PostgreSQL supports a precision of up to 19 digits. |
Server-Level Roles | The sysadmin server-level role is supported. Other server-level roles apart from sysadmin are not supported. |
Database-Level Roles Other Than db_owner | The db_owner database-level role is supported. Other database-level roles apart from db_owner are not supported. |
SQL Keyword SPARSE | The keyword SPARSE is accepted and ignored. |
SQL Keyword Clause ON filegroup | This clause is currently ignored. |
SQL Keywords CLUSTERED and NONCLUSTERED for Indexes and Constraints | The MSSQL Compatible Edition accepts and ignores the keywords CLUSTERED and NONCLUSTERED. |
sysdatabases.cmptlevel | sysdatabases.cmptlevel is always NULL. |
tempdb Not Reinitialized upon Restart | Permanent objects (such as tables and procedures) created in tempdb are not deleted when the database is restarted. |
TEXTIMAGE_ON Filegroup | The MSSQL Compatible Edition ignores the TEXTIMAGE_ON filegroup clause. |
Time Precision | The MSSQL Compatible Edition supports precision up to six decimal places for fractional seconds. It is anticipated that this behavior will not have negative impacts. |
Transaction Isolation Levels | Treats READUNCOMMITTED in the same way as READCOMMITTED. REPEATABLEREAD and SERIALIZABLE are not supported. |
Virtual Computed Columns (Non-Persisted) | Virtual computed columns are created as persisted columns. |
WITHOUT SCHEMABINDING Clause | This clause is not supported for functions, procedures, triggers, or views. |
SELECT charindex(LOWER('x'), LOWER(a COLLATE sql_latin1_general_cp1_cs_as)) FROM t1
CREATE function f_charindex(@s1 varchar(max), @s2 varchar(max)) returns intASBEGINdeclare @i int = 1WHILE len(@s2) >= len(@s1)BEGINif LOWER(@s1) = LOWER(substring(@s2,1,len(@s1))) return @iset @i += 1set @s2 = substring(@s2,2,999999999)ENDreturn 0ENDgo
Database users (non-DBO) can perform the following actions: | SQL Server | Babelfish |
Can objects be created in the schema without extra authorization from the DBO? | No | Yes |
Can referenced objects created by the DBO in the schema be used without extra authorization? | Yes | No |
Was this page helpful?