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

Why use PostgreSQL as a .NET Developer?

PostgreSQL is known as the most advanced open-source relational database in the world. It has some very notable features that make it more advanced than other relational databases. PostgreSQL is an Object-Relational Mapper Database (ORMD), which means it has ORM support right out of the box.

Features

  • Table Inheritance
  • Text Search – Natural Language Processing (NLP)
  • Supports Programming Languages: Ruby, Perl, Python, TCL, PL/pgSQL, SQL, .NET, JavaScript
  • Fully SQL Compliant
  • ACID Compliance
  • Can create custom data types
  • Includes data types for objects like IP Addresses
  • Row Level Security
  • NoSQL Support (JSON/XML)
  • JSON
  • Native partitioning
  • Fully open-source

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 for 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)

Square Brackets “[ ]”

I’ve used square brackets “[ ]” before to smooth out MSSQL keywords like state. This doesn’t work in Postgres.

Anonymous Code Blocks

In MSSQL you could create a new query and write T-SQL by declaring variables, making select statements, performing any sort of CRUD operation and everything was easy. This is not the case for pgAdmin or PostgreSQL. You can still do basic SELECT statements against tables but anything above and beyond that gets rather tricky if you aren’t familiar with PostgreSQL.

PostgreSQL: Documentation 45.5 Anonymous Blocks

DO

In order to write anonymous SQL You must wrap the block in a DO statement. This must specify the language as plpgsql because SQL does not allow anonymous code blocks.

Variables

Variables must be declared in the DO block. They cannot be declared else where within an anonymous block.

SELECT Doesn’t Work the Same

In the context of an anonymous block, SELECT will not work as it did in SQL Server Management Studio (SMSS). If you wanted to see specific bits of data you could use the RAISE NOTICE function.

Casting

Casting is done a little differently in PostgreSQL and I like it a lot. Instead of using CONVERT(VARCHAR, @VARIABLE) in PostgreSQL you can use data::string.

Date Time Objects

PostgreSQL has some really cool functions for managing date-time and timestamps.

Note: I HIGHLY recommend watching Rob Conery’s video on “Postgres for .NET Developers”. I learned some of these tricks from him. Also, I found this video long after I started this blog post… This post was originally for notes for learning Postgres. Comparing it to what I already know seemed to help.

Functions (Stored Procedures)

Functions in Postgres act very similar to Stored Procedures in MSSQL. Yet, Postgres also has Stored Procedures that act like functions in MSSQL.

To query this function run this command.

Text-Searching Natural Language Processing (NLP)

Microsoft SQL Server does not support text searching out of the box. Using something like Lucene would give that capability. However, PostgreSQL has text-searching capabilities built into their database engine.

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.

Further Reading

https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/
https://www.pluralsight.com/courses/postgres-dotnet-developers