| layout | title | nav_order | has_children | permalink |
|---|---|---|---|---|
default |
Limitations of Babelfish |
3 |
false |
/docs/limitations-of-babelfish |
Some SQLServer features cannot be ported to PostgreSQL for various reasons.
The following is a list of functions that are not supported by Babelfish:
| MSSQL Function | Babelfish potential workaround |
|---|---|
| app_name | SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid(); |
| approx_count_distinct | |
| assemblyproperty | |
| asymkey_id | pgcrypto |
| asymkeyproperty | pgcrypto |
| atn2 | atan2 |
| cast_and_convert | |
| cert_id | Use OpenSSL flag at build time |
| certencoded | Use OpenSSL flag at build time |
| certprivatekey | Use OpenSSL flag at build time |
| certproperty | Use OpenSSL flag at build time |
| checksum_agg | |
| col_length | User Defined Function |
| col_name | SELECT attname FROM pg_attribute WHERE attrelid = object_id('public.info') and attnum = 1 |
| columnproperty | User defined function to match property names |
| columns_updated | SQL allows you to define aliases for the "old" and "new" rows or tables for use in the definition of the triggered action (e.g., CREATE TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW ROW AS othername ...). More information here |
| compress | Gzip extension |
| connections | Can only get active/idle connections with SELECT count(*) FROM pg_stat_activity; |
| context_info | |
| cpu_busy | |
| crypt_gen_random | Use gen_random_uuid() with pgcrypto |
| current_request_id | |
| current_timezone | |
| current_transaction_id | |
| database_principal_id | See pg_database, pg_db_role_setting, pg_roles |
| datediff_big | Need to create UDF similar to one in this post using DATE_PART |
| datefirst | SELECT setting FROM pg_settings WHERE name = 'datefirst' |
| datetimeoffsetfromparts | |
| dbts | Use txid_currrent() |
| decompress | |
| decryptbyasymkey | pgcrypto |
| decryptbycert | pgcrypto |
| decryptbykey | pgcrypto |
| decryptbykeyautoasymkey | pgcrypto |
| decryptbykeyautocert | pgcrypto |
| decryptbypassphrase | pgcrypto |
| difference | Use extension fuzzystrmatch |
| encryptbyasymkey | pgcrypto |
| encryptbycert | pgcrypto |
| encryptbykey | pgcrypto |
| encryptbypassphrase | pgcrypto |
| eomonth | |
| eventdata | |
| file_id | |
| file_idex | |
| file_name | |
| filegroup_id | |
| filegroup_name | |
| filegroupproperty | |
| fileproperty | |
| filepropertyex | |
| format | Currently, there is no overrtide for the PF Format command so it behaves completely different than T-SQL. The to_char function is the closest match to T-SQL format.) |
| fulltextcatalogproperty | |
| fulltextserviceproperty | |
| get_filestream_transaction_context | |
| getansinull | |
| grouping_id | Use grouping() |
| has_perms_by_name | Need to use a combination of the access privilege inquire functions |
| host_id | pg_backend_pid() |
| host_name | inet_client_addr() |
| identity_function | nextval() with PG SEQUENCE object |
| idle | |
| index_col | pg_index view |
| indexkey_property | pg_index view |
| indexproperty | pg_index view |
| io_busy | |
| is_member | select pg_has_role(CURRENT_USER,'pg_signal_backend', 'MEMBER') |
| is_objectsigned | |
| is_rolemember | select pg_has_role('sa', 'pg_signal_backend','MEMBER') |
| isjson | create a PL/pgSQL function using return expression (p_json::json is not null) |
| json_modify | create a PL/pgSQL function to wrap jsonb_set() to return a varchar value |
| json_query | create a PL/pgSQL function to wrap jspnb_path_query to return varchar value |
| json_value | select cast(jsonb_path_query('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}','$.info.address[0].town') as varchar(100) ); |
| key_guid | pgcrypto |
| key_id | pgcrypto |
| key_name | pgcrypto |
| langid | |
| language | |
| last_value | |
| lead | Use PostgreSQL window function lead |
| lock_timeout | select s.setting FROM pg_catalog.pg_settings s where name = 'lock_timeout' |
| loginproperty | Use columns in pg_roles, but SQL logins have more properties than PG |
| max_connections | select s.setting FROM pg_catalog.pg_settings s where name = 'max_connections' |
| min_active_rowversion | |
| next_value_for | |
| object_definition | Use PG System Catalog Information Functions like pg_get_constraintdef, pg_get_expr, pg_get_functiondef, pg_get_indexdef, pg_get_ruledef, pg_get_triggerdef, pg_get_veiwdef based on the object type for the specified name. |
| object_schema_name | Use pg_namespace joined to pg_class, pg_proc, etc to look up the name |
| objectproperty | Use pg_class and others to look for the property |
| objectpropertyex | Use pg_class and others to look for the property |
| opendatasource | |
| openjson | |
| openquery | |
| openrowset | |
| openxml | |
| options | |
| original_db_name | |
| original_login | |
| pack_received | |
| pack_sent | |
| packet_errors | |
| parse | |
| partition | |
| percentile_cont | |
| percentile_disc | |
| permissions | |
| pwdcompare | |
| pwdencrypt | |
| remserver | |
| rowcount_big | |
| schema_id | Use pg_class, pg_proc, etc to get relnamespace |
| servicename | |
| session_context | |
| session_id | |
| session_user | |
| sessionproperty | |
| signbyasymkey | |
| signbycert | |
| soundex | use extension fuzzystrmatch - includes soundex() |
| square | power(n, 2) |
| stats_date | |
| stdev | stddev |
| stdevp | stddev_pod |
| str | to_char(125.856, '999D9') |
| string_agg | |
| suser_name | |
| suser_sid | There is no SID concept in PostgreSQL |
| switchoffset | |
| symkeyproperty | pgcrypto |
| system_user | current_user |
| textsize | select s.setting FROM pg_catalog.pg_settings s where name = 'babelfish_pg_tsql.text_size' |
| textvalid | |
| timeticks | |
| todatetimeoffset | |
| total_errors | |
| total_read | |
| total_write | |
| trigger_nestlevel | pg_trigger_depth |
| try_cast | |
| try_convert | |
| try_parse | |
| txtptr | |
| type_name | format_type(c.user_type_id,null) |
| typeproperty | Use pg_catalog.pg_type view |
| user_id | Use pg_catalog.pg_roles view |
| user_name | Use pg_catalog.pg_roles view |
| var | Use variance |
| varp | Use var_pop |
| verifysignedbyasymkey | pgcrypto |
| verifysignedbycert | pgcrypto |