I recently started a new job, and they work with PostgreSQL databases. I posted on Postgres a while back (https://sqlrob.com/2012/12/04/postgresql/) but I needed to refresh my memory on the differences with SQL Server, especially with querying.

I’ve used Azure Data Studio for querying both SQL Server and Postgres, the PostgreSQL extension will enable access to those systems. There’s also SQL Fiddle (http://sqlfiddle.com) where you can create database objects and query them, for SQL Server, Postgres, Oracle, MySQL and SqlLite.

Learning a little about Oracle at my last job, it seemed a lot of differences were around booleans, dates, and limiting records returned. This turned out to be true for Postgres as well. I was a little confused navigation in Postgres and not finding any Stored Procedures. Turns out that Functions give us this functionality in Postgres.

Here’s a test table for SQL Server:

CREATE TABLE Test(
TestId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name varchar(20) NULL,
ItemCount int NULL,
CreatedDate datetime NOT NULL DEFAULT GETDATE(),
CreatedBy varchar(10) NOT NULL,
IsActive bit DEFAULT 1
);

And here’s the same Test table for Postgres:

CREATE TABLE Test(
TestId serial PRIMARY KEY,
Name varchar(20) NOT NULL,
ItemCount int NULL,
CreatedDate timestamp NOT NULL DEFAULT localtimestamp,
CreatedBy varchar(10) NOT NULL,
IsActive bool NOT NULL DEFAULT true
);

A lot is the same for both systems, but we do have a few differences. A big difference is with auto-generated number fields. Where SQL Server uses Identity, Postgres makes use of Sequences (Which are available in SQL Server as well.) Postgres gives us Serial, which will create a Sequence linked to the column, with an incrementing integer value, and make the column Not Nullable.

Another difference is with date/times. We use datetime for SQL Server, and timestamp for Postgres. When setting a default GETDATE() gives us the current date and time in SQL Server, where we’ll call localtimestamp in Postgres.

The last difference with our test tables is with boolean values. SQL Server uses bit, which can store a value of 0 or 1. Postgres uses boolean, where we use true or false.

An interesting thing was running the CREATE TABLE for Postgres gave me a table where all the column names were in lower case, even though I used Pascal case. Turns out that you need to delimit the column names with double quotes to prevent the names from being converted to lower case. This doesn’t create an issue with querying, I can still query for TestId even if the name was converted to testid.

Now for querying. If I want to return the most recently created record, here are queries for both databases:

-- SQL Server
SELECT TOP 1 * FROM Test ORDER BY CreatedDate DESC;

-- Postgres
SELECT * FROM Test ORDER BY CreatedDate DESC LIMIT 1;

String concatenation in Postgres uses pipes instead of pluses:

SELECT Name || ' ' || 'CreatedBy' as StringConcat FROM Test;

In SQL Server, we would use DATEPART and DATENAME is extract parts of dates and format these date parts. In Postgres:

SELECT
date_part('day', CreatedDate) as CreatedDay,
date_part('month', CreatedDate) as CreatedMonth,
date_part('year', CreatedDate) as CreatedYear,
to_char(CreatedDate, 'month') as CreatedMonthNameLower,
to_char(CreatedDate, 'Month') as CreatedMonthName,
to_char(CreatedDate, 'MONTH') as CreatedMonthNameUpper,
to_char(CreatedDate, 'MM-dd-yyyy') as CreatedDateFormatted
FROM Test;

In this example, date_part gives the numeric values (11 for November, etc) and to_char can give a string version. It was interesting that the casing mattered within the to_char call, all lower gives the month name as november, etc.

With incrementing dates, we would use the DATEADD function in SQL Server. Postgres allows us to add an interval to a date:

SELECT localtimestamp as Now, localtimestamp + Interval '1 day' as OneDayFromNow

I was glad to see that we can run a SELECT without a table here, like we can in SQL Server, instead of selecting like DUAL as you would have to do in Oracle.

This was enough to get me started, but I’m sure there’s plenty of other differences as well. I’ll be back with a follow up post once I uncover more.

Postgres Documentation: https://www.postgresql.org/docs/13/index.html