PostgreSQL-Specific Features

1. Introduction

PostgreSQL follows the SQL standard closely, but it also has many features that other databases don’t have, or implement differently. If you come from SQL Server, knowing these differences saves you hours of debugging.

In this lesson you will see the most useful data types, JSONB, UPSERT and RETURNING, some handy extras, the psql tool, and how to connect from .NET with Npgsql and EF Core. Examples use an engineering domain: projects, equipment, documents and tags.

2. Data types

2.1 Identity vs serial

For auto-incrementing keys, modern PostgreSQL uses identity columns (SQL standard). The old serial type still works, but it is a shortcut that creates a separate sequence with looser ownership rules.

CREATE TABLE project (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        text        NOT NULL UNIQUE,
    name        text        NOT NULL,
    budget      numeric(14,2),
    is_active   boolean     NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

• GENERATED ALWAYS: you cannot insert a value manually (unless you use OVERRIDING SYSTEM VALUE).
• GENERATED BY DEFAULT: the database generates a value only if you don’t provide one. This is what EF Core with Npgsql uses by default.

2.2 uuid

CREATE TABLE document (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),  -- built-in since PG 13
    project_id  bigint NOT NULL REFERENCES project(id),
    title       text   NOT NULL
);

UUIDs are useful when IDs are generated by many systems (integration!) or on the client. They take 16 bytes, so they are bigger than bigint (8 bytes).

2.3 Text, numbers, dates

Type	Use it for	Note
text	Any string	No performance difference with varchar(n)
varchar(n)	Strings with a real length rule	Only adds a length check
numeric(p,s)	Money, exact quantities	Exact, but slower than double precision
boolean	True/false	Real type, not bit like SQL Server
timestamptz	Points in time	Stored as UTC, shown in the session time zone
timestamp	Local “wall clock” time	No time zone info: avoid for events
date	A calendar day	No time part

2.4 Arrays

PostgreSQL columns can hold arrays of any type.

CREATE TABLE equipment (
    id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tag         text   NOT NULL,
    name        text,
    labels      text[] NOT NULL DEFAULT '{}'
);

INSERT INTO equipment (tag, labels) VALUES ('P-101', ARRAY['pump', 'critical']);

SELECT tag FROM equipment WHERE 'critical' = ANY(labels);     -- contains one value
SELECT tag FROM equipment WHERE labels @> ARRAY['pump'];       -- contains all values

Npgsql maps string[] and List<string> to text[] automatically. Arrays are great for simple lists of labels; for anything with its own attributes, use a separate table.

2.5 Enum types

CREATE TYPE revision_status AS ENUM ('Draft', 'InReview', 'Approved', 'Superseded');

ALTER TYPE revision_status ADD VALUE 'Cancelled';  -- adding is easy, removing is not

Enums are compact and type-safe, but changing them is harder than changing a lookup table or a text column with a CHECK constraint.

3. JSONB

JSONB stores JSON in a binary, indexed format. It is perfect for attributes that differ by equipment type: a pump has a flow rate, a valve has a pressure class.

ALTER TABLE equipment ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}';

UPDATE equipment
SET    attributes = '{"manufacturer": "Grundfos", "flowRate": 120, "ports": ["inlet", "outlet"]}'
WHERE  tag = 'P-101';

3.1 Operators

Operator	Returns	Example
->	jsonb	attributes -> 'ports'
->>	text	attributes ->> 'manufacturer'
#>>	text by path	attributes #>> '{ports,0}'
@>	contains	attributes @> '{"manufacturer": "Grundfos"}'
?	key exists	attributes ? 'flowRate'

SELECT tag, (attributes ->> 'flowRate')::int AS flow_rate
FROM   equipment
WHERE  attributes @> '{"manufacturer": "Grundfos"}'
  AND  (attributes ->> 'flowRate')::int > 100;

-- Change one key without rewriting the others
UPDATE equipment
SET    attributes = jsonb_set(attributes, '{flowRate}', '150')
WHERE  tag = 'P-101';

3.2 Indexing JSONB

-- General index: supports @>, ?, ?| and ?&
CREATE INDEX ix_equipment_attributes ON equipment USING GIN (attributes);

-- Expression index for one frequently filtered key
CREATE INDEX ix_equipment_manufacturer ON equipment ((attributes ->> 'manufacturer'));

See the indexes lesson for more about GIN and B-tree indexes.

4. UPSERT and RETURNING

4.1 INSERT … ON CONFLICT

An UPSERT inserts a row, or updates it if it already exists. This is very common in integration jobs that sync data from another system.

CREATE UNIQUE INDEX ux_equipment_project_tag ON equipment (project_id, tag);

INSERT INTO equipment (project_id, tag, attributes)
VALUES (1, 'P-101', '{"manufacturer": "KSB"}')
ON CONFLICT (project_id, tag)
DO UPDATE SET attributes = EXCLUDED.attributes;   -- EXCLUDED = the row you tried to insert

-- Or simply ignore duplicates
INSERT INTO equipment (project_id, tag) VALUES (1, 'P-101')
ON CONFLICT DO NOTHING;

ON CONFLICT needs a unique constraint or unique index on the conflict columns. It is atomic, so it is safe under concurrency (unlike “SELECT, then INSERT or UPDATE” in application code).

4.2 RETURNING

RETURNING gives back data from rows that were inserted, updated or deleted, in the same round trip.

INSERT INTO project (code, name) VALUES ('PRJ-2026-01', 'Offshore platform')
RETURNING id, created_at;

DELETE FROM document WHERE project_id = 1
RETURNING id, title;   -- see exactly what you deleted

5. Other handy features

5.1 ILIKE

ILIKE is a case-insensitive LIKE. In PostgreSQL, LIKE is case-sensitive.

SELECT tag, name FROM equipment WHERE name ILIKE '%pump%';

In EF Core: db.Equipment.Where(e => EF.Functions.ILike(e.Name, "%pump%")).

5.2 generate_series

generate_series produces a set of numbers or dates. It is great for test data and for reports with no gaps.

INSERT INTO equipment (project_id, tag)
SELECT 1, 'P-' || lpad(n::text, 4, '0')
FROM   generate_series(1, 1000) AS n;

-- Documents created per day, including days with zero
SELECT d::date AS day, count(doc.id) AS created
FROM   generate_series('2026-09-01'::date, '2026-09-30'::date, interval '1 day') AS d
LEFT JOIN document doc ON doc.created_at::date = d::date
GROUP  BY d
ORDER  BY d;

5.3 Schemas

A schema is a namespace inside a database. Every database has a public schema by default.

CREATE SCHEMA integration;
CREATE TABLE integration.sync_log (id bigint GENERATED ALWAYS AS IDENTITY, message text);

SET search_path TO integration, public;   -- where unqualified names are looked up

Schemas help to separate modules (or tenants) and to manage permissions per area.

6. psql basics

psql is the official command-line client. It’s always available, also inside a Docker container.

psql -h localhost -p 5432 -U postgres -d cadmatic
# inside Docker:
docker exec -it my-postgres psql -U postgres -d cadmatic

Command	Meaning
\l	List databases
\c dbname	Connect to another database
\dn	List schemas
\dt	List tables
\d equipment	Describe a table (columns, indexes, FKs)
\x	Toggle expanded output (one column per line)
\timing	Show execution time of each query
\q	Quit

7. PostgreSQL vs SQL Server

Topic	SQL Server	PostgreSQL
Limit rows	SELECT TOP 10	LIMIT 10 OFFSET 20
Null fallback	ISNULL(a, b)	COALESCE(a, b) (standard)
Current time	GETDATE()	now()
String concat	+	||
Quoted names	[Name]	"Name"
Unicode text	NVARCHAR	text (database is UTF-8)
Boolean	bit	boolean
Auto increment	IDENTITY(1,1)	GENERATED AS IDENTITY
Clustered index	Yes, usually on PK	No, tables are heaps
Default concurrency	Locking (unless RCSI)	MVCC
Procedural code	T-SQL	PL/pgSQL functions and procedures
License	Commercial	Open source

8. Npgsql and EF Core

Npgsql is the .NET data provider for PostgreSQL. The EF Core provider is the NuGet package Npgsql.EntityFrameworkCore.PostgreSQL.

// Program.cs
var connectionString = builder.Configuration.GetConnectionString("Cadmatic");
// "Host=localhost;Port=5432;Database=cadmatic;Username=app;Password=secret"

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention());

PostgreSQL features in the model:

public class Equipment
{
    public long Id { get; set; }
    public string Tag { get; set; } = "";
    public string? Name { get; set; }
    public List<string> Labels { get; set; } = [];           // text[]
    public EquipmentAttributes Attributes { get; set; } = new();
}

public class EquipmentAttributes
{
    public string? Manufacturer { get; set; }
    public int? FlowRate { get; set; }
}

// In OnModelCreating
modelBuilder.HasDefaultSchema("projects");
modelBuilder.Entity<Equipment>().OwnsOne(e => e.Attributes, a => a.ToJson()); // jsonb
modelBuilder.Entity<Equipment>().HasIndex(e => e.Labels).HasMethod("gin");

You can then write LINQ like db.Equipment.Where(e => e.Labels.Contains("critical")) and Npgsql translates it to array SQL. For the general EF Core concepts see ORM and Entity Framework and part 2.

9. Interview questions

Q: Why would you choose PostgreSQL over another relational database? PostgreSQL is open source, very standards-compliant and has a strong ecosystem. It offers powerful features like JSONB, arrays, rich index types and good concurrency through MVCC. It also runs everywhere, including Docker and all major clouds, without licence costs.

Q: What is the difference between json and jsonb? The json type stores the original text and re-parses it on every access. The jsonb type stores a parsed binary format, so it is faster to query and it supports GIN indexes and operators like containment. In practice you almost always use jsonb.

Q: When would you use a JSONB column instead of normal columns? I use JSONB for flexible attributes that change by type, for example different properties for pumps and valves, or for storing payloads from external systems. Core fields, keys and relationships stay in normal columns with constraints. If I query a JSON key often, I add an expression index or move it to a real column.

Q: How do you implement an upsert in PostgreSQL? I use INSERT ... ON CONFLICT on a unique constraint, with DO UPDATE SET and the EXCLUDED pseudo-table to take the new values. It is a single atomic statement, so it’s safe when several processes sync data at the same time. If I only want to skip duplicates, I use DO NOTHING.

Q: What is the difference between serial and identity columns? Both generate auto-increment numbers using a sequence. serial is an old PostgreSQL shortcut, while identity columns are standard SQL and tie the sequence more cleanly to the column. For new tables I use GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY.

Q: What differences did you notice moving from SQL Server to PostgreSQL? The main ones are syntax details like LIMIT instead of TOP, COALESCE instead of ISNULL, and double quotes for identifiers, which are folded to lowercase. PostgreSQL has no clustered indexes and uses MVCC by default. With EF Core I also had to be careful with DateTime kinds and timestamptz.

Q: How do you connect a .NET application to PostgreSQL? I install the Npgsql.EntityFrameworkCore.PostgreSQL package and register the DbContext with UseNpgsql and a connection string from configuration. Then I use migrations as usual. For raw high-performance access I can also use Npgsql directly or Dapper on top of it.

10. Quiz

Mettiti alla prova

0/8 risposte

1. Which column definition is the modern, standard way to create an auto-increment key?

   Aid serial PRIMARY KEYBid int IDENTITY(1,1) PRIMARY KEYCid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEYDid int AUTO_INCREMENT PRIMARY KEY

2. Which type should you use to store the moment a revision was approved?

   AtimestamptzBtimestampCtextDdate

3. What does the operator ->> return when applied to a jsonb column?

   AA jsonb valueBA booleanCAn arrayDA text value

4. Which index type is used for jsonb containment queries with @>?

   AB-treeBGINCHashDClustered

5. In INSERT ... ON CONFLICT ... DO UPDATE, what is EXCLUDED?

   AThe row you tried to insertBThe rows that were deletedCA list of ignored columnsDThe old row before the update

6. What is the PostgreSQL equivalent of SQL Server's SELECT TOP 10?

   ASELECT FIRST 10BSELECT ... FETCH 10CSELECT ... ROWNUM 10DSELECT ... LIMIT 10

7. Which psql command describes the columns and indexes of a table?

   A\l equipmentB\d equipmentC\dt equipmentD\x equipment

8. Which method registers the PostgreSQL provider for a DbContext?

   AUseSqlServerBUsePostgresCUseNpgsqlDAddPostgreSql

Controlla le risposte

11. Exercises

11.1 Equipment with flexible attributes

Goal: model equipment with type-specific attributes using JSONB.

1. Create project and equipment tables with identity keys, a timestamptz column and a jsonb attributes column.
2. Insert 3 pumps and 3 valves with different attribute keys.
3. Write a query that returns all pumps from one manufacturer with a flow rate above 100.
4. Add a GIN index and check with EXPLAIN that it is used.

Hint: with only a few rows the planner prefers a seq scan. Use generate_series to insert a few thousand rows first.

11.2 Idempotent tag import

Goal: import tags from an external system without creating duplicates.

1. Create a tag table with a unique constraint on (project_id, code).
2. Write an INSERT ... ON CONFLICT ... DO UPDATE that updates the description when the tag already exists.
3. Add RETURNING id, (xmax = 0) AS inserted and run the import twice.

Hint: xmax = 0 is a well-known trick to see if the row was inserted (true) or updated (false).

11.3 Connect an ASP.NET Core API

Goal: run a minimal API on PostgreSQL with EF Core.

1. Start PostgreSQL 16 in Docker and connect to it with psql.
2. Add Npgsql.EntityFrameworkCore.PostgreSQL and EFCore.NamingConventions to a new Web API project.
3. Register the DbContext with UseNpgsql and snake case naming, then create a migration for Project and Equipment (with a List<string> Labels).
4. Inspect the created tables with \d equipment and verify the text[] column.

Hint: docker run -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16.