Entity Framework Core & PostgreSQL – differences from the perspective of using Microsoft SQL Server

Hey Guys!

From this article You will learn about:

  • Standard PostgreSQL management tool – pgAdmin,
  • Differences in build SQL queries from TSQL dialect,
  • Base differences in the search engine.

PgAdmin – Microsoft SQL Management PostgreSQL alternative.

Like Microsoft SQL Server, PostgreSQL also has an official Management tool – PgAdmin. It’s a cross-platform, web tool (yes, it’s occurred at browser level, but before using it, you must install a local Pg Admin server at Your device). My first view of that approach was a very negative, SQL Management tool in the browser, really? Does it work? Now, I know. It works perfectly & from every device (If You using multiple systems Windows | Mac | Linux. You want to use it :-)).

Overview of pgAdmin in version 4.

Like You see in the above screenshot, the first view after choosing the database is a very readable & clean dashboard about current connections & traffic. It’s very helpful because You can do some reaction at the right moment (I know, what I wrote :-), this feature rescue me a few times in pool connections context).

What about backups, generate scripts & execution plan?

Backups & scripts generating options are possible to do like in Microsoft solution. The right mouse button on the database name opening a context menu which a lot of possibility :-).

Execution plans? No problem ;-)!

If You want more usefully information about pgAdmin, please check the Official Documentation: https://www.pgadmin.org/docs/pgadmin4/4.23/getting_started.html

There is also a possible connect PostgreSQL from Azure Data Studio, but it has smaller features than original pgAdmin. I recommend to using official pg tool!

Differences in SQL queries from Microsoft SQL (TSQL) dialect

There are also a few differences in building SQL queries. I will describe it in the below sample:

SELECT "Id", 
	"CreatedAt", 
	"PriceChanged", 
	"LastPrice", 
	"DayVolume", 
	"PairId"
	FROM public."Volumes" 
	LIMIT 1000;

Soooooo, like You see Microsoft styled square brackets ‘[]’ are changed to ‘ “” ‘. Here we also have one, very big difference. PostgreSQL SQL Engine by default is case sensitive, so when You will try to skip “, this query will be invalid. Why? Because on the below layers, PostgreSQL translates all column names to lower chars (in the original, these column names are started from the big letter, as a result, the engine can’t find them). Don’t ask me why, I don’t know, but If You know, please let this information in comment :-). Also, like You probably saw, the TOP keyword is transformed to LIMIT with a changed place :-). So if You want to see this query in Microsoft SQL dialect…

SELECT TOP 1000 Id, 
	CreatedAt, 
	PriceChanged, 
	LastPrice, 
	DayVolume, 
	PairId
	FROM dbo.Volumes 

Also, the “public” namespace is default namespace like “dbo” in MS.

Base differences in the search engine

Quick question on start, the result of theses query will be the same?

SELECT "Id", 
	"Email", 
	"Hash", 
	"Salt", 
	"LastActivity"
	FROM public."Users" where "Email" = 'patryk.roguszewski@the-worst.dev'
SELECT "Id", 
	"Email", 
	"Hash", 
	"Salt", 
	"LastActivity"
	FROM public."Users" where "Email" = 'Patryk.Roguszewski@the-worst.dev'

Not in default PostgreSQL collation. From PostgreSQL v12 developers introduced the case_insensitive option, but by default, the engine is set to case sensitive (more information: https://www.postgresql.org/docs/current/collation.html). So the size of the letters has matter!

Before v12 one of the possibilities to do case insensitive queries were ILIKE option | to lower or install citext extensions.

Summary

The rest things are very similar. Migrations in EF works perfectly. There are also small differences in index types, but there is a great information about it in official docs (https://www.postgresql.org/docs/9.5/indexes-types.html).

Thanks for reading & remember, size of the letters has matter in PostgreSQL!