PL/pgSQL vs T-SQL

Here’s a quick reference of common differences between PostgreSQL’s PL/pgSQL and SQL Server’s T-SQL.

Data Types

PL/pgSQL T-SQL Notes
BOOL BIT Postgres stores values true/false, SQL Server stores 1 or 0 but note that this cannot be aggregated by the SUM() aggregate function. If you want to use the SUM() function against these data types, use a Case expression to map the BOOL or BIT values to an integer 1 and 0.
Timestamp Datetime / Datetime2 Postgres supports a much wider range of values – from 4713 BC to 294276 AD, SQL Server Datetime supports values 1753 AD to 9999 AD and Datetime2 0001 AD to 9999 AD. SQL Server also has SMALLDATETIME which only consumes 4 bytes storage space but in doing so only supports time precision HH:MM and year range 1900 AD to 2079 AD
Text | Character Varying | Varchar VARCHAR(MAX) Postgres supports a max string length of 1gb whereas SQL Server Varchar(max) can hold values up to 2gb in size. An important difference in using Varchar or Character Varying without the optional length parameter; in Postgres this results in a data type with the upper limit of 1gb. In SQL Server, the default behaviour of omitting the length is a single character data type i.e. Varchar = Varchar(1) and Char = Char(1)

 

Functions

PL/pgSQL T-SQL Notes
Now() Getdate() Or just use the ANSI standard CURRENT_TIMESTAMP in either DBMS
SESSION_USER Original_Login()

 

DDL – Create Table

PL/pgSQL T-SQL Notes
SERIAL INT IDENTITY(1,1) Serial and Smallserial are shortcuts to setting up a Sequence object in the background with a Default constraint against the column it’s used against that calls the Nextvalue function of the Sequence created. Edit the sequence if you want to modify the Min, Max and Increment properties.
SMALLSERIAL SMALLINT IDENTITY(1,1)

 

Examples

PL/pgSQL T-SQL

 

 

 

References

Leave a Reply

Your email address will not be published. Required fields are marked *