Structural Differences between the SQL Server Database and the PostgreSQL Database

This page documents differences between the current SQL Server database and the future PostgreSQL database. Some datatypes and functions that exist in one SQL implementation do not exist in the other. This page will grow and change as the project moves on.

Type Differences

Some Types that we use in SQL Server don't exist in PostgreSQL or they have different names. Here’s a list:

  • decimal(int, int) is called numeric(int, int). Note that decimal(int, int) can still be used and is automatically cast to numeric(int, int).

  • char(int) is called character(int).

  • nvarchar(int) is called varchar(int).

  • uniqueidentifier is called uuid (which stands for “universal unique identifier”).

  • varbinary(int) does not exist. ByteA is used instead as it serves the same purpose.

  • datetime does not exist. Instead either timestamp with timezone or timestamp without timezone should be used. In its migration Django has consistently chosen to use timestamp with timezone so this trend was continued.

Differences in Function Syntax

According to wikipedia, PostgreSQL’s PL/pgSQL, which is the language of choice for functions and procedures, closely resembles the PL/SQL language that Oracle uses. If You have experience with Oracle databases You should feel right at home. But openIMIS uses SQL Server, so I have compiled here all the differences I noticed (and remembered) into a list:

 

Did you encounter a problem or do you have a suggestion?

Please contact our Service Desk



This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. https://creativecommons.org/licenses/by-sa/4.0/