After spending decades working in SQL Server, getting familiar with PostgreSQL felt like traveling to a country where everyone speaks a language I mostly understand – but with a different accent and some unfamiliar slang.

At first glance, SQL is SQL. But once I started writing real queries in PostgreSQL, I realized that my habits from T-SQL didn’t always carry over. Some keywords are different, some functions are missing, and some behaviors are surprisingly… better.

If you’re a SQL Server professional starting to explore PostgreSQL – either by choice or by business necessity – this post will walk you through five key SQL language differences that caught me off guard, and how I adapted to them.

1. TOP vs. LIMIT: Learning to End a Query Differently

The first difference I encountered was also the most obvious. I tried to limit rows in a query using TOP – just like I always had in SQL Server:

SELECT TOP 5 * FROM person.person ORDER BY modifieddate DESC;

PostgreSQL didn’t like that one bit. The error message pointed me toward LIMIT, a clause I barely remembered existed:

SELECT * FROM person.person ORDER BY modifieddate DESC LIMIT 5;

It turns out that LIMIT is not only the standard SQL way to restrict rows, but it also pairs beautifully with OFFSET, making pagination much cleaner:

SELECT * FROM person.person ORDER BY modifieddate DESC LIMIT 5 OFFSET 10;

Coming from SQL Server, where you often have to reach for ROW_NUMBER() or the more verbose OFFSET – FETCH syntax, this felt refreshingly concise.

2. Auto-Incrementing: IDENTITY Isn’t Universal

In SQL Server, creating a table with an auto-incrementing primary key is second nature. You use IDENTITY(1,1) and call it a day:

CREATE TABLE customers
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   name NVARCHAR(100)
);

When I tried the same pattern in PostgreSQL, the parser had other plans. PostgreSQL doesn’t use IDENTITY in the same way – at least not by default. Instead, I was pointed toward two alternatives: the older SERIAL keyword and the newer, SQL-standard GENERATED AS IDENTITY. Here’s the modern way to do it in PostgreSQL:

CREATE TABLE customers
(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name TEXT
);

3. GETDATE() vs. now() / CURRENT_TIMESTAMP: Asking for the Time

Another surprise came when I reached for a familiar tool: fetching the current date and time. In SQL Server, you’d simply use:

SELECT GETDATE();

But PostgreSQL doesn’t recognize GETDATE(). Instead, it gives you two interchangeable options:

SELECT now();

-- or
SELECT CURRENT_TIMESTAMP;

Both return the current date and time with time zone awareness, depending on your server settings. While now() is more concise, CURRENT_TIMESTAMP is part of the SQL standard – and thus favored in more portable or formal SQL code.

It’s a small change, but one that hits you quickly when writing logging logic, default values for timestamp columns, or reports based on system time. You’ll also notice that PostgreSQL has separate functions for CURRENT_DATE, CURRENT_TIME, and LOCALTIMESTAMP, giving you a more expressive toolbox.

4. ISNULL() vs. COALESCE(): Cleaning Up Your NULLs the Standard Way

Another habit that didn’t translate was my reliance on ISNULL(). In SQL Server, this was the go-to for defaulting NULL values:

SELECT ISNULL(middlename, '') FROM person.person;

PostgreSQL has no such function. Instead, it uses the ANSI standard COALESCE():

SELECT COALESCE(middlename, '') FROM person.person;

This makes your logic both more flexible and more portable. And because COALESCE() works in both SQL Server and PostgreSQL, it’s worth using even in T-SQL moving forward.

5. DATEPART vs. EXTRACT: Working with Timestamps

Working with parts of dates – like extracting the year or the month – is a common task. In SQL Server, we do this with DATEPART:

SELECT DATEPART(YEAR, modifieddate) FROM person.person;

But PostgreSQL doesn’t have DATEPART. Instead, it uses the SQL-standard EXTRACT function, which looks slightly different:

SELECT EXTRACT(YEAR FROM modifieddate) FROM person.person;

Want the month? Just change the keyword:

SELECT EXTRACT(MONTH FROM modifieddate) FROM person.person;

This pattern works for all parts of a timestamp – day, hour, minute, second, even ISO week or day of the week. And while the syntax feels verbose at first, it’s actually consistent and composable. You can even cast the result to an integer if needed:

SELECT EXTRACT(DAY FROM modifieddate)::INT FROM person.person;

It’s also worth noting that PostgreSQL supports date/time arithmetic and intervals far more flexibly than SQL Server, opening up elegant expressions like:

WHERE modifieddate >= now() - INTERVAL '7 days'

So while it may take a minute to shift your thinking from DATEPART to EXTRACT, the transition comes with some added power.

Final Thoughts: Learning a New SQL Accent

Switching from SQL Server to PostgreSQL is less like switching languages and more like learning a new dialect – one where the grammar is stricter, the idioms are different, and some expressions simply don’t exist.

But once you get used to PostgreSQL’s quirks, it rewards you with expressive syntax, better standards compliance, and a vibrant ecosystem. The moment you stop trying to write SQL Server-style code and start writing idiomatic PostgreSQL, everything begins to click.

So yes, your first few days may be filled with syntax errors and mild frustration. But stick with it. PostgreSQL has its own rhythm – and once you find it, you might be surprised how elegant it feels.

If you are more interested in how to transition your SQL Server knowledge to PostgreSQL, I will run my online live training PostgreSQL for the SQL Server Professional later this year. Get in contact with me for further information and a 10% discount code.

Thanks for your time,

-Klaus