How PostgreSQL Is Compatible with SQL Server

In this article, the author explains three different methods to demonstrate the compatibility of PostgreSQL with SQL server.

By digoal

Background

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.

Method 1: citext Type

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)

Method 2: Coverage = Operator

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)

Method 3: Plug-in for MCHAR and MVARCHAR Provided by Postgres Pro

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:

Case insensitive comparison
Handling of the whitespace at the end of a string
These types are always stored as two-byte Unicode values regardless of database encoding

You can learn more about it here: https://postgrespro.com/docs/postgrespro/9.6/mchar

References

0 0 0

Share on

: digoal

277 posts | 24 followers
Follow

Community

How PostgreSQL Is Compatible with SQL Server

Background

Method 1: citext Type

Method 2: Coverage = Operator

Method 3: Plug-in for MCHAR and MVARCHAR Provided by Postgres Pro

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL