×
Community Blog How PostgreSQL Is Compatible with SQL Server

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

210 posts | 13 followers

You may also like

Comments