Reading Time: 3 minutes

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

GitHub: .NET Repository Pattern

Running in Docker

PostgreSQL can easily be ran locally in Docker.

docker pull postgres

https://hub.docker.com/_/postgres

PostgreSQL Client

There are a few PostgreSQL clients worth mentioning. I prefer free and very stable reliable tools.

DBeaver Community

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.

https://dbeaver.io/download/

pgAdmin

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.

https://www.pgadmin.org/

PostgreSQL Languages

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.

PostgreSQL: G.3. Procedural Languages

This means you can create a function that runs in Python.

https://www.postgresql.org/docs/9.0/plpython-funcs.html

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.

Declaring Variables

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.

INT

BIGINT

VARCHAR / TEXT

In PostgreSQL you have several options for a specified character length.

character(100)
char(100)
TEXT

ISNULL()?

So PostgreSQL doesn’t support an ISNULL() function however you can use NULLIF() or COALESCE().

MSSQL

SET @FirstName = COALESCE(NULLIF(@FirstName,”), NULL)

PostgreSQL

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.

GitHub: .NET Repository Pattern

PostgreSQL .NET Connection String

I’ve stored this connection string in the appsettings.json file.