Some differences between PostgreSQL and sqlserver

Time:2022-7-7

Conditional query – fuzzy matching

The fuzzy matching like of PostgreSQL and SQL server is different. The like of PostgreSQL is case sensitive, and SQL server is not.

The tests are as follows:

//Construct data SQL
create table t_user (
 id integer PRIMARY KEY,
 name varchar(50) not null,
 code varchar(10)
);

insert into t_user values(1,'Zhangsan','77771');
insert into t_user values(2,'Lisi',null);

Execute the following SQL in PostgreSQL and SQL server respectively:


select * from t_user where name like '%zhang%';

PostgreSQL results:

SQL Server results:

If you want PostgreSQL like to be case insensitive, you can use Ilike


select * from t_user where name ilike '%zhang%';

Or use lower or upper to convert to lowercase or uppercase and then fuzzy matching. This way of SQL is compatible with both databases.


select * from t_user where upper(name) like upper('%zhang%');
select * from t_user where lower(name) like lower('%zhang%');

Conditional query – weak type matching

PostgreSQL is strongly typed when doing conditional queries, but SQL server is weakly typed.

Execute the following SQL in PostgreSQL and SQL server respectively:


select * from t_user where code = 77771; 

Code is a varchar type of data.

PostgreSQL results:

SQL Server results:

Conditional query – blank at the end

If there is blank space at the end of SQL server query, SQL server will ignore it, but PostgreSQL will not.

Execute the following SQL in PostgreSQL and SQL server respectively:


select * from t_user where code = '77771  '; 

PostgreSQL results:

SQL Server results:

SQL server can find data, but PostgreSQL can’t.

order by

1. The default order by behavior of PostgreSQL and SQL server is inconsistent.

2. If the field of order by is null, PostgreSQL will put it in front, and SQL server will put it in the back.

Execute the following SQL in PostgreSQL and SQL server respectively:


select * from t_user order by code desc;

PostgreSQL:

SQL Server:

It can be seen that the order of the data found is different.

In some cases, if the data order is required to be consistent in the two databases, you can add nulls last in the query SQL of PostgreSQL to make null data lag.


select * from t_user order by code desc nulls last;

You can also use case when to unify SQL:


ORDER BY (case when xxx is null then '' else xxx end) DESC;

String splicing

SQL server uses the “+” sign to splice strings, and the concat function is not supported before the 2012 version.

PostgreSQL uses “|” to splice strings, and supports concat function.

Query whether the table exists


//SQL Server
select count(name) from sys.tables where type='u' and name='t_user';

//PostgreSQL
select count(table_name) from information_schema.tables where table_name='t_user';

Supplement: sqlserver and PostgreSQL database field type comparison table

As follows:


 sqlserver      to    postgresql type
// "bigint",            "bigint"
//  "binary",             "bytea"
//  "bit",              "boolean"
//  "char",              "char"
//  "datetime",            "timestamp"  
//  "decimal",            "numeric"
//  "float",            "double precision"
//  "image",            "bytea"
//  "int",              "integer"
//  "money",            "numeric(19,4)"
//  "nchar",            "varchar"
//  "ntext",            "text"
//  "numeric",            "numeric"
//  "nvarchar",            "varchar"
//  "real",              "real"
//  "smalldatetime",        "timestamp"
//  "smallint",            "smallint"
//  "smallmoney",          "numeric(10,4)"
//  "text",              "text"
//  "timestamp",          "bigint"
//  "tinyint",            "smallint"
//  "uniqueidentifier",        "uniqueidentifier"
//  "varbinary",          "bytea"
//  "varchar",            "varchar"

The above is my personal experience. I hope I can give you a reference, and I hope you can support developeppaer. If there is any mistake or failure to consider completely, please don’t hesitate to comment.