PostgreSQL for .NET Developers
Venturing into microservice architecture has forced me to become more aware of other technologies that may be more performant, cheaper, and or a better match for that specific need. PostgreSQL (“Postgres”) is a relational database that is very comparable to Oracle. It’s not that different than MSSQL but has some serious quirks. The more I learn about Postgres, the bigger the fan I’m becoming.
Sample .NET Project with PostgreSQL and Dapper .NET
Running in Docker
PostgreSQL can easily be ran locally in Docker.
docker pull postgres
docker run -d \
--name some-postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v /custom/mount:/var/lib/postgresql/data \
There are a few PostgreSQL clients worth mentioning. I prefer free and very stable reliable tools.
I personally prefer DBeaver when working with Postgres. It’s very easy to use and a very versatile tool that is capable of interfacing other databases including MSSQL, MySQL, and MongoDB.
pgAdmin runs in the browser as a web app that interfaces the Postgres database. This is a very viable option and is very well designed.
This is certainly a feature to PostgreSQL that makes it rather unique. With PostgreSQL, there are several procedural languages included with the base distribution: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. There are also extensions that support other languages like Java, PHP, Python, R, Ruby, Scheme, and Unix shell.
This means you can create a function that runs in Python.
CREATE FUNCTION pymax (a integer, b integer)
if a > b:
$$ LANGUAGE plpythonu;
PostgreSQL (PL/pgSQL) vs MSSQL
Switching to PostgreSQL has been a bit challenging to me. It’s really not the syntax that’s the issue. There are a lot of slightly different ways of doing things.
So, this was confusing but in a stored procedure I needed to declare the variable before the BEGIN statement.
SQL Statements Must End with a Semicolon.
All SQL statements needs to end with a semicolon, “;”.
PostgreSQL Data Types
For the most part this is very similar to MSSQL but there are some caveats and things are different.
VARCHAR / TEXT
In PostgreSQL you have several options for a specified character length.
So PostgreSQL doesn’t support an ISNULL() function however you can use NULLIF() or COALESCE().
SET @FirstName = COALESCE(NULLIF(@FirstName,”), NULL)
Square Brackets “[ ]”
I’ve used square brackets “[ ]” before to smooth out MSSQL keywords like state. This doesn’t work in Postgres.
DBeaver: Creating a Stored Procedure
Yeah, so this took me a few minutes to figure out. It’s not as intuitive as I’d like it to be. Creating a stored procedure in DBeaver is easy. You just need to right-click “Functions” and select “Create New Function”. This pops up a dialog where there is an option to change the Type to “Stored Procedure”. I also prefer setting the language to “plpgsql”.
Dapper .NET with PostgreSQL
I’ve set up a repository that includes Dapper .NET and interfaces Postgres.
PostgreSQL .NET Connection String
I’ve stored this connection string in the appsettings.json file.
"ConnectionString": "User ID=dbuser; Password=555gpah4jzetczpf; Host=localhost; Port=5432; Pooling=true;Min Pool Size=0;"