Help Center/ TaurusDB_TaurusDB for PostgreSQL/ Kernel/ Babelfish/ Functionality Partially Supported by Babelfish

Updated on 2025-11-28 GMT+08:00

View PDF

Functionality Partially Supported by Babelfish

Babelfish is extending its support for T-SQL functionality in each release to enhance compatibility. However, some functions and behaviors are still not fully supported. This section lists the differences between Babelfish and T-SQL and provide some suggestions on how to address them.

Views

Table 1 lists the SQL Server system views that cannot be found in Babelfish.

**Table 1** SQL Server system views that cannot be found in Babelfish
View	Description
sys.parameters	Contains parameters of stored procedures, functions, and some other objects.
sys.periods	Stores definitions of the periods for temporal tables (history tables).
sys.service_queues	Contains configurations of Service Broker service queues.
sys.sql_dependencies	Records dependencies among database objects.
sys.stats_columns	Displays information about the columns involved in statistics objects.
sys.masked_columns	Contains columns that dynamic data masking is applied to.
sys.memory_optimized_tables_internal_attributes	Contains internal attributes of memory-optimized tables.
sys.numbered_procedure_parameters	Contains parameters of numbered stored procedures.
sys.allocation_units	Displays information about allocation units (storage structures) in a database.
sys.event_notifications	Contains configurations of event notifications.
sys.extended_procedures	Displays information about extended stored procedures.
sys.function_order_columns	Contains information about the sequence column in the common language runtime (CLR) table-valued function.
sys.trigger_event_types	Lists the type of each event that causes a trigger to fire.

Differences in How a ROWVERSION/TIMESTAMP Value Is Assigned

SQL Server assigns a unique ROWVERSION/TIMESTAMP value to each row inserted (INSERT) or updated (UPDATE). Babelfish assigns the same ROWVERSION/TIMESTAMP value to all rows modified in a given statement.

For example, when an UPDATE or INSERT SELECT operation that affects multiple rows is executed:

SQL Server generates different values for the ROWVERSION/TIMESTAMP column of each row.
Babelfish (PostgreSQL) assigns the same value to all affected rows.

Limited Compatibility with the JSON Data Type

Babelfish does not directly support SQL Server's native JSON data type. Instead, it is stored as the NVARCHAR, VARCHAR, or TEXT data type. For more information, see JSON data in SQL Server and JSON data type.

Babelfish provides a set of JSON functions that can be used to handle JSON data. The functions are:

JSON_VALUE
JSON_QUERY
OPENJSON
JSON_MODIFY
ISJSON

Identifier Differences

If an index named ix1 in T-SQL is on table t1, the index will internally be renamed ix1t1a5111d2a1767bc43a700e9f2162be019 (ix1t1 + hash value) by Babelfish.

T-SQL transparency: Users still use the original identifiers to compile queries, and Babelfish automatically maps the identifiers to internal names.
PostgreSQL visibility: The actual object name seen in PostgreSQL (using psql) contains a hash value.

Babelfish uses a hash algorithm to dynamically adjust identifiers. This approach is compatible with the lengthy names and flexible rules of SQL Server while also meeting the strict requirements of PostgreSQL. T-SQL users are unaware of this processing, but each object name seen in PostgreSQL has a hash suffix. For more information, see Babelfish identifiers.

Impact of identifier differences in Babelfish on system views:

If an object name contains 64 or fewer characters, the identifier differences have no impact on accessing the object through SQL commands (such as USE and SELECT), and the original object name is stored in system views such as sys.databases, sys.indexes, and sys.tables.
If an object name contains more than 64 characters, the identifier differences still have no impact on accessing the object through SQL commands (such as USE and SELECT), but the object name is stored in the format of partial prefix + hash value in system views such as sys.databases, sys.indexes, and sys.tables. The original object name cannot be restored through simple string processing.

Impact scenarios of identifier differences:

In the migration link with a Babelfish instance as the destination, DRS pre-checks the destination Babelfish instance before the migration. During the pre-check, DRS queries sys.* system views to check whether objects exist. If any database objects in the source SQL Server instance have names exceeding 64 characters, the pre-check fails.
When a user accesses sys.* system views, objects that contain more than 64 characters cannot be found.

System View Differences

The following views differ.

sys.hash_indexes

Column missing: The is_ignored_in_optimization column is missing in Babelfish.
Data type matching: The data types in the name column are the same (sysname is an alias for nvarchar, so there is no compatibility issue). The same applies to sys.triggers, sys.procedures, and sys.check_constraints.

sys.index_columns

The column_store_order_ordinal column is missing in Babelfish.

Click to enlarge

sys.system_objects and sys.all_objects

sysname: It is an alias for nvarchar.
type: It exists in both SQL Server and Babelfish.

sys.objects

In Babelfish, similar to SQL Server, sys.objects also has the type column with a data type of char(2). The same applies to sys.sequences.

sysname is an alias for nvarchar, so there is no compatibility issue.

sys.schemas

Click to enlarge

Babelfish also has the schema_id and principal_id columns with a data type of oid. The oid type is indeed a four-byte integer, so there is no compatibility issue. For details, see Object Identifier Types.

sys.sql_modules

The inline_type and is_inlineable columns are missing in Babelfish.

Click to enlarge

sys.computed_columns

sysname is an alias for nvarchar, so there is no compatibility issue.

Another difference is that SQL Server uses tinyint while Babelfish uses int. The int type is larger than the tinyint type, so there is no compatibility issue when applications are migrated from SQL Server to Babelfish.

Click to enlarge

The Microsoft documentation lists the constraints on tinyint and int.

Click to enlarge

The same applies to sys.table_types and sys.types.

In addition, the columns in the following figure are missing in sys.computed_columns of Babelfish.

Click to enlarge

sys.sysdatabases

The following table lists the difference.

Click to enlarge

The text variable is larger than sysname (128 characters), so there is no compatibility issue.

sys.sysprocesses

The spid, blocked, and dbid columns are defined as smallint in SQL Server and as int in Babelfish.
The type ID of the uid column is not defined in Babelfish. According to the PostgreSQL documentation, the type ID is currently implemented as an unsigned four-byte integer (oid).
Also, the type ID of the context_info column is not defined. According to the PostgreSQL documentation, the data type can store binary strings (bytea).
loginname (SQL Server) and loginame (Babelfish): The "n" character is missing in Babelfish.
The page_resource column is missing in Babelfish.

Impact: Functions are not affected.

sys.key_constraints

sysname is an alias for nvarchar(128).

Babelfish does not have the is_enforced column. In SQL Server, this column indicates whether the PRIMARY KEY or UNIQUE constraint is enforced, that is, whether SQL Server checks uniqueness during data modification.

sys.default_constraints

sysname is an alias for nvarchar(128). modified_date is equivalent to modify_date.

Click to enlarge

sys.stats

sysname is an alias for nvarchar(128). The auto_drop column is missing in Babelfish.

sys.indexes

The following columns are missing in Babelfish.

Column	Purpose	Applies To
compression_delay	Postpones the compression of row groups.	Columnstore indexes
suppress_dup_key_messages	Suppresses detailed prompts about duplicate key errors.	Unique indexes
optimize_for_sequential_key	Optimizes the insertion performance of incremental key indexes and reduces insert contention.	B-tree indexes (such as indexes on auto-increment columns)
is_ignored_in_optimization	Ignores an index during query optimization.	Any index

sys.identity_columns

The following columns are missing in Babelfish.

Column	Description
is_data_deletion_filter_column	Controls whether a column is used to filter data deletion operations in the ledger view.
ledger_view_column_type	Specifies the numeric type of a column in the ledger view. It is used for internal storage.
ledger_view_column_type_desc	Specifies the descriptive type of a column in the ledger view (for example, GENERATED ALWAYS or APPEND_ONLY).
is_dropped_ledger_column	Controls whether a column that originally belonged to a ledger table has been dropped.
graph_type	Specifies the numeric type of a column in a graph. It is used for internal storage.
graph_type_desc	Specifies the descriptive type of a column in a graph (such as NODE or EDGE).

sys.syscharsets

SQL Server uses smallint and tiny, while Babelfish uses int. The details are no longer described.

sys.assembly_modules/sys.synonyms

sysname is an alias for nvarchar(128). The details are no longer described.

Unmatched Table Metadata

Unmatched table metadata is an expected difference caused by the architecture difference between SQL Server and PostgreSQL (Babelfish's underlying engine). The text representation in columns is different, but functionally, the returned metadata is equivalent. So, the functionality of applications using Babelfish is not affected. Table metadata in the following views does not match.

sys.views
sys.columns
sys.databases
sys.foreign_keys
sys.all_views
sys.partitions
sys.sql_expression_dependencies
sys.all_columns
sys.tables

Parameters

Babelfish does not support the following settings:

SET ANSI_NULL_DFLT_OFF ON

SET ANSI_NULL_DFLT_ON OFF

SET ANSI_PADDING OFF

SET ANSI_WARNINGS OFF

These server-side settings will prevent the SET ANSI_DEFAULTS command from working properly.

The following parameters cannot be modified.

Click to enlarge

For more information, see parameters that should not be modified in the official Babelfish documentation.

Cursors

Babelfish does not support SET CURSOR_Closed_ON_COMMIT ON. Also, Babelfish does not recognize cursors that are dynamically defined in stored procedures or functions. You can rewrite these cursors using temporary tables.

The following items are not supported:

Updatable cursors
Global cursors
Cursor extraction behaviors: FETCH PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE
Cursor output parameters (Variables and parameters of cursor types cannot be used as output parameters, or an error will be reported.)
Cursor options: SCROLL, KEYSET, DYNAMIC, FAST_FORWARD, SCROLL_LOCKS, OPTIMISTIC, TYPE_WARNING, and FOR UPDATE

In addition, the Babelfish community documentation states: "When ANSI_DEFAULTS is turned on, it enables the following settings: ANSI_NULLS, ANSI_NULL_DFLT_ON, IMPLICIT_TRANSACTIONS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS and CURSOR_CLOSE_ON_COMMIT. The driver and provider set CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF (so CURSOR_CLOSE_ON_COMMIT is actually OFF at connect time, though ANSI_DEFAULTS is ON)."

Commands

Babelfish does not support SET STATISTICS or SET STATISTICS_ALL, but provides the following alternatives:
- SET BABELFISH_STATISTICS PROFILE [ON|OFF]: Set this function to ON for the actual query execution plan. This function implements the functionality of EXPLAIN ANALYZE in PostgreSQL.
- SET BABELFISH_SHOWPLAN_ALL [ON|OFF]: Setting this function to ON will generate an estimated query execution plan. This function implements the functionality of EXPLAIN in PostgreSQL. Use this command to obtain the explanation plan of a given query.
In addition, the Babelfish community documentation states: "Starting with version 2.2.0, you can set the escape_hatch_showplan_all parameter to ignore in order to avoid the use of BABELFISH_ prefix in the SQL Server syntax for SHOWPLAN_ALL and STATISTICS PROFILE SET commands."

To use them as SET SHOWPLAN_ALL and SET STATISTICS PROFILE without the BABELFISH_ prefix, run:
```
EXEC sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_showplan_all', 'ignore';
```
Or:
```
EXEC sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_showplan_all', 'strict';
```
You can use sp_babelfish_configure to change parameter settings. Example:
```
execute sp_babelfish_configure 'explain_buffers', 'on'; 
```

Babelfish does not support SET SHOWPLAN_XML.
Babelfish can output query plans in XML format. For details about how to check Babelfish query plans, see Reviewing a Query Plan.

Before running a query, you can visualize the statistics profile by executing SET BABELFISH_STATISTICS PROFILE ON.
- Run the following command to set explain_format:
```
execute sp_babelfish_configure 'explain_format', 'xml'
```
- Run the following commands to visualize the statistics profile:
```
select set_config('babelfishpg_tsql.explain_format', 'xml', false);
```
```
SET BABELFISH_STATISTICS PROFILE ON;
```

Babelfish does not support SET FORCEPLAN.
Babelfish does not support SET SHOWPLAN_TEXT.
Babelfish only supports OFF for SET NOEXEC. For details, see unsupported functionality in the Babelfish community documentation.
Babelfish does not support SET TEXTSIZE, but provides the following alternative:
Babelfish sets the TEXTSIZE parameter to 4096 during session initialization. To change the value, perform the following operations.

If the value of -y is smaller than that of TEXTSIZE, the output text size cannot be adjusted. In this case, the value of -y is used.
1. Use sqlcmd to connect to a Babelfish instance.
```
sqlcmd -U babelfish_user -P 12345678 -No -y <textsize_value>
```
2. Set the TEXTSIZE parameter.
```
SET TEXTSIZE <textsize_value>
```
3. Query the value of TEXTSIZE.
```
SELECT id, LEFT(name, CAST(current_setting('babelfishpg_tsql.textsize') as int )) AS name FROM <table_name>; 
```
```
SELECT * FROM <table_name>;
```

Functions

The following functions differ:

DATABASE_PRINCIPAL_ID()

You can:

Leave this parameter blank. The database principal ID of the current user is retrieved.
Enter the specified database principal name as a string, for example, ('db_owner').

You are advised not to pass an empty string: DATABASE_PRINCIPAL_ID (''). For more information, see DATABASE_PRINCIPAL_ID.

FULLTEXTSERVICEPROPERTY()

In Babelfish, the function always returns 0 regardless of the input parameter because Babelfish does not support full-text search.

In SQL Server, if you specify an invalid property, NULL will be returned. If full-text search has been installed and enabled and you specify a valid property, for example, FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'), 1 will be returned.

concat_ws

A string concatenation function in PostgreSQL. It concatenates multiple strings (or text values) into one string using a specified separator.

Syntax:

concat_ws(separator, str1, str2, ..., strN)

Note: The first argument is used as the separator string and cannot be NULL, or NULL will be returned. Other arguments can be NULL, which will be automatically ignored. For details, see the official PostgreSQL documentation. This is the same for Babelfish and PostgreSQL.

To use this function properly, do not set the first argument to NULL.

HAS_PERMS_BY_NAME

A security-related function in SQL Server. It checks whether the current user has a specified permission on a specified database object.

Syntax:

HAS_PERMS_BY_NAME (
securable,
securable_class,
permission
)

Arguments:

securable: name of a securable on which the permission is to be checked. It can be a database object such as a table, view, or stored procedure. If the value is NULL, the securable is the current database.
securable_class: securable class.
The value can be DATABASE, OBJECT, SCHEMA, TABLE, or VIEW.
permission: permission to be checked. The value can be SELECT, INSERT, UPDATE, or DELETE.

Return values:

1: The user has the specified permission.
0: The user does not have the specified permission.
NULL: Arguments are invalid or the query fails.

Differences between SQL Server and Babelfish:

If the securable name is NULL but the securable class is specified, SQL Server considers that the current database permission is checked and returns 1.
Babelfish requires that both arguments be NULL or not NULL at the same time, or NULL will be returned.

Unmatched Server Identity Authentication Output

If the authentication method is set to trust, PostgreSQL allows login without password authentication, which is the root cause of the problem.

To ensure that password authentication is enforced, you must verify that the pg_hba.conf file does not use the trust method for the problematic user.

Perform the following steps:

Open the pg_hba.conf file, which is usually located in the PostgreSQL data directory (for example, /home/postgres/taurus-postgres/pg/install/data/pg_hba.conf or similar).
Find the entries related to a specific user or connection type (for example, local or host).
Ensure that the authentication method is set to md5 (instead of trust), which will force password authentication.

Click to enlarge

The outputs of the DATEADD function do not match. Run the following command:

SELECT 'DATEADD - NULL Interval' AS TestCase, DATEADD(DAY, NULL, '2025-04-25') AS Result;

SQL Server reports an error, indicating NULL is not a valid argument. Babelfish returns NULL.

Cause: The DATEADD function has been declared STRICT. STRICT indicates that the function always returns null whenever any of its arguments are null. For details, see the official PostgreSQL documentation.

Time Precision

SQL Server: SQL Server supports fractions of a second for precision of the datetime2 data type to 7 digits with 3 fractional digits. SQL Server rounds this to 3-millisecond precision. This means that when you store a particular time value, the milliseconds might be rounded and end up being stored differently than what you specified.
Example:
```
SELECT CAST('2016-12-26 23:59:59.999' as datetime)
```
Return value: 2016-12-27 00:00:00.000
Babelfish: PostgreSQL only supports 6-digit precision for fractional seconds. It does not perform SQL Server 3-millisecond rounding.

String Types

Notes about TEXT and VARCHAR(MAX):

Microsoft SQL Server has deprecated the TEXT data type (Reference). You are advised to use VARCHAR(MAX).
In native Microsoft SQL Server, VARCHAR(MAX) may be affected by the 8,060-byte row size limit in some operations (such as sorting and indexing), causing errors (such as Error 512).
Babelfish runs in a PostgreSQL environment, where VARCHAR does not have a length limit by default unless specified as VARCHAR(n). This means:
- TEXT can still be used in Babelfish, and no error is reported for excessively long text (unlike Microsoft SQL Server behavior).
- VARCHAR(MAX) is not limited to 8,000 bytes in Babelfish, allowing for storage of larger text (in line with PostgreSQL).
If you need to strictly comply with Microsoft SQL Server behavior, avoid using TEXT and pay attention to the row limit scenarios of VARCHAR(MAX).