In this article, the author explains three different methods to demonstrate the compatibility of PostgreSQL with SQL server. In particular, we'll discuss case neglect and case insensitivity.
postgres=> create extension citext ; CREATE EXTENSION postgres=> create table abc (id int, info citext); CREATE TABLE postgres=> insert into abc values (1,'HelloworD'); INSERT 0 1 postgres=> select * from abc where info='helloword'; id | info ----+----------- 1 | HelloworD (1 row)
postgres=> create or replace function ci_cmp(text,text) returns boolean as $$ postgres$> select lower($1)=lower($2); postgres$> $$ language sql strict immutable; CREATE FUNCTION postgres=> create operator = (function=ci_cmp , leftarg=text , rightarg=text); CREATE OPERATOR postgres=> select 'hello' OPERATOR(public.=) 'Hello'; ?column? ---------- t (1 row)
It implements the types MCHAR and MVARCHAR, which are bug-to-bug compatible with MS SQL CHAR and VARCHAR respectively. Additionally, these types use libicu for comparison and case conversion, so their behavior is identical across different operating systems.
Postgres Pro also includes a citext extension that provides types similar to MCHAR. But this extension doesn't emulate MS-SQL behavior concerning end-of-value whitespace.
Differences from Postgres Pro standard CHAR and VARCHAR are:
You can learn more about it here: https://postgrespro.com/docs/postgrespro/9.6/mchar
ApsaraDB - August 12, 2020
Alibaba Clouder - February 13, 2021
ApsaraDB - March 19, 2020
Alibaba Clouder - February 5, 2021
Alibaba Cloud Community - March 28, 2022
digoal - April 27, 2021
Fully managed and less trouble database servicesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
Leverage cloud-native database solutions dedicated for FinTech.Learn More
More Posts by digoal