PostgreSQL provider

Use nextorm.postgres for PostgreSQL; it renders @name parameters, limit/offset paging, coalesce, extract date parts and stddev/variance aggregates, supports INTERSECT ALL/EXCEPT ALL, and requires double-quoted aliases.

Prerequisites: Provider overview · Quickstart

Overview

PostgresDataContext (src/nextorm.postgres/PostgresDataContext.cs) wraps Npgsql. It creates an NpgsqlConnection and passes null parameter values as DBNull (Npgsql rejects a null parameter value).

PostgresDialect (src/nextorm.postgres/PostgresDialect.cs) is the dialect:

parameter placeholder @name;
string concatenation with ||;
MakeCoalesce renders coalesce(a, b);
boolean literals are true/false;
identifiers use double quotes (Escape returns "name"), and MakeColumnReference quotes a name too so that a quoted alias survives when it is referenced from an outer query;
derived tables and table-valued functions must be aliased (RequireSubqueryAlias is true, and its consequence is that an alias is always emitted);
INTERSECT ALL / EXCEPT ALL are supported (SupportsIntersectExceptAll is true);
arrays are supported (SupportsArrays is true): array parameters with the any/all quantifiers and the array functions, with two per-name exceptions (SupportsArrayFunction): array_reverse is refused on every PostgreSQL version, and array_sort renders only from an explicit 18+;
JSON/JSONB is supported (SupportsPostgresJsonSql is true, PostgreSQL only; native jsonb storage is SupportsJson): the json_agg/jsonb_agg aggregates, the construction/access functions and the ->/->>/@>/? operators, with JsonDocument/JsonElement/ JsonNode parameters bound as jsonb;
greatest/least and the aggregate FILTER (WHERE ...) clause are enabled (SupportsGreatestLeast is true and AggregateFilterStyle is AnsiFilter when the configured server version is unset or 9.4+; on a version below 9.4 the dialect rejects filtered aggregates with NotSupportedException);
date_trunc is enabled (SupportsDateTrunc is true);
date arithmetic is enabled (SupportsDateArithmetic is true): SqlFunctions.Sql.date_add/end_of_month and the DateTime.Add* methods render PostgreSQL interval arithmetic (x + (n * interval '1 day'), date_trunc('month', x) + interval '1 month - 1 day');
a TimeSpan column maps a PostgreSQL interval only for zero-month values: a returned interval carrying a non-zero month component (either sign) is rejected with InvalidCastException naming calendar months, zero-month days and microseconds convert exactly with checked arithmetic (an unrepresentable value fails with OverflowException instead of wrapping), time/timetz columns use the driver's TimeSpan accessor, and a mapped TimeOnly projection is supported for a time column (µs exact, with the day domain enforced);
the string_agg/array_agg aggregates are enabled (SupportsStringArrayAggregates is true);
full-text search is enabled (SupportsFullText is true): SqlFunctions.Sql.contains renders to_tsvector(col) @@ plainto_tsquery(search) and freetext websearch_to_tsquery(search); ranking is available through the native SqlFunctions.Postgres.ts_rank/ts_rank_cd/ts_headline (gated by SupportsTextSearchFunctions);
the extended scalar function library is enabled (SupportsExtendedScalarFunctions is true): additional math (asin, cbrt, mod, ...), string (split_part, lpad, initcap, ...), POSIX regular expression (regexp_replace, regexp_like, ...), date/time (make_interval, justify_days, justify_hours, to_char, to_date, ...), num_nulls/num_nonnulls and the type helper pg_typeof;
the session/information functions are enabled (SessionInfoFunctions is true): SqlFunctions.Sql.current_user/session_user/current_schema render the key words and current_database/version render current_database()/version();
the UUID generators are enabled (UuidGenerators is true): SqlFunctions.Sql.gen_random_uuid() renders gen_random_uuid() (PostgreSQL 13+), and uuidv7() renders uuidv7() only when the configured server version is an explicit 18 or later; below 18 and when the version is unset the call is refused at preparation with NotSupportedException and no v4 UUID is substituted;
the boolean, bitwise, statistical and ordered-set aggregates are enabled (SupportsBooleanAggregates, SupportsBitAggregates, SupportsStatisticalAggregates and SupportsOrderedAggregates are true): bool_and/bool_or/every, bit_and/bit_or/bit_xor, corr/covar_*/regr_* and percentile_cont/percentile_disc/mode with WITHIN GROUP;
the ordered-set percentile surface adds the median composition (SqlFunctions.Postgres.median(() => x) → percentile_cont(0.5) within group (order by x), an aggregate: one row per group) under the separate SupportsOrderedPercentiles flag. The continuous form interpolates, so percentile_cont and median require a floating-point sort expression and refuse an integer/numeric one (The percentile_cont function on PostgreSQL requires a floating-point sort expression.); percentile_disc keeps the sort expression's type. A fraction is a double in [0, 1], not a percentage, and is validated before rendering; the generic SqlFunctions.Postgres.percentile has no native spelling and throws NotSupportedException;
aggregate names are remapped: stdev→stddev, stdevp→stddev_pop, var→variance, varp→var_pop;
Math.Log maps to ln(...) (PostgreSQL's log() is base 10);
SqlFunctions.Postgres.log(base, x) renders log((base)::numeric, (x)::numeric): the only two-argument overload is log(numeric, numeric), so both operands are cast, and a null operand yields SQL NULL;
Math.Round(x, digits) casts a double/float first argument to numeric (round((x)::numeric, digits)): PostgreSQL has no round(double precision, integer);
SqlFunctions.Postgres.setseed(seed) renders setseed(seed) (gated by SupportsRandomSeed); PostgreSQL's setseed returns void, so a projected value is always null;
DateTime.Now renders now(), DateTime.UtcNow renders now() at time zone 'utc';
date parts render as extract(part from value); SqlFunctions.Sql.extract(part, value) covers quarter/week (ISO)/dow/isodow and SqlFunctions.Sql.date_part("epoch", value) renders cast(extract(epoch from value) as double precision);
paging is limit n / limit n offset m; an offset-only query emits offset m alone.

The variability aggregates follow the shared cardinality contract: Stdev/Var are null for zero or one contributing value, Stdevp/Varp are null for zero and 0.0 for one. The result is a real Nullable<T>, so a sample over an empty or single-row set materializes as null.

Registering the provider

Two overloads are available on DataContextBuilder (src/nextorm.postgres/DI/PostgresDataContextOptionsBuilderExtensions.cs):

using NextORM.Core;
using NextORM.Postgres;

var byString = new DataContextBuilder().UsePostgres("Host=localhost;Database=app;Username=app;Password=secret");

using var connection = new Npgsql.NpgsqlConnection("Host=localhost;Database=app;...");
var byConnection = new DataContextBuilder().UsePostgres(connection);

using var ctx = byString.CreateDataContext();   // IDataContext

Directly:

using NextORM.Core;
using NextORM.Postgres;

using IDataContext ctx = new PostgresDataContext("Host=localhost;Database=app;...", new DataContextBuilder());

Server version

The dialect can be pinned to the server version, which gates version-dependent syntax. Configuration is explicit — nextorm never probes the live server. Pass the version with the context constructor's Version parameter; leaving it unset keeps the historical behaviour and assumes PostgreSQL 9.4 or later, so the ANSI aggregate FILTER (WHERE ...) clause stays enabled. A version below 9.4 disables it: a filtered aggregate rejects with NotSupportedException.

The per-name array and UUID functions are a deliberate exception to that permissive unset default. PostgreSQL provides no array_reverse in any current release and added array_sort and uuidv7 only in 18, so an unset version refuses all three at preparation instead of assuming a version. An explicit 18+ enables array_sort/uuidv7; array_reverse stays refused on every version. No other unset-version gate changes, and no fallback (NULL, an unchanged array, a v4 UUID) is substituted.

// Unset: filtered aggregates are enabled (assumed 9.4+), while array_reverse, array_sort and uuidv7 are refused.
using var current = new PostgresDataContext("Host=localhost;Database=app;...", new DataContextBuilder());

// Pin 9.3: the aggregate `FILTER (WHERE ...)` clause is rejected.
using var legacy = new PostgresDataContext("Host=localhost;Database=app;...", new DataContextBuilder(), new Version(9, 3));

// Pin 17: array_sort() and uuidv7() are refused at preparation too; array_reverse is always refused.
using var pg17 = new PostgresDataContext("Host=localhost;Database=app;...", new DataContextBuilder(), new Version(17, 0));

// Pin 18: array_sort() and uuidv7() render natively; array_reverse is still refused.
using var pg18 = new PostgresDataContext("Host=localhost;Database=app;...", new DataContextBuilder(), new Version(18, 0));

The version is immutable and fixed per concrete context type for the process lifetime. Two contexts of the same concrete type that request different versions throw InvalidOperationException; to run against several versions in one process, declare a distinct context subclass per version:

public sealed class Postgres93DataContext : PostgresDataContext
{
    public Postgres93DataContext(string connectionString, DataContextBuilder builder)
        : base(connectionString, builder, new Version(9, 3)) { }
}

Paging

ctx.From<ISimpleEntity>().Page(5, 10).Select(x => x.Id);   // limit 5 offset 10
ctx.From<ISimpleEntity>().Offset(10).Select(x => x.Id);    // offset 10

select id
 from simple_entity limit 5 offset 10
select id
 from simple_entity offset 10

OFFSET may appear on its own, but LIMIT must come first when both are present. Because PostgreSQL does not require an injected sort, no ORDER BY is added.

Coalesce, date parts and aggregates

var query = ctx.From<IComplexEntity>()
    .Select(x => new
    {
        Fallback = x.String ?? "",
        Year = x.Datetime!.Value.Year,
    });

select coalesce(somestring, '') as "Fallback", extract(year from dt) as "Year"
 from complex_entity

var stdev = ctx.From<IComplexEntity>().Select(x => SqlFunctions.Sql.stdev((double)x.Id));  // stddev(...)
var varp  = ctx.From<IComplexEntity>().Select(x => SqlFunctions.Sql.varp((double)x.Id));   // var_pop(...)

count and count_big both render count(*), because PostgreSQL's count already returns a 64-bit integer.

Arrays

PostgreSQL is the only supported provider with native arrays, and the array surface lives on Postgres (Sql stays cross-provider). An array operand is passed as a single parameter, so column = any(@array) works with a runtime parameter or a captured array, and the SQL does not depend on the number of elements:

var ids = new long[] { 1, 2, 3 };

ctx.From<IComplexEntity>().Where(e => SqlFunctions.Postgres.any(e.Id, ids));      // (id = any(@p0))
ctx.From<IComplexEntity>().Where(e => e.Id == SqlFunctions.Postgres.any(ids));    // id = any(@p0)
ctx.From<IComplexEntity>().Where(e => e.Id == SqlFunctions.Postgres.any(SqlFunctions.Parameter<long[]>(0))); // id = any(@norm_p0)

select id
 from complex_entity where (id = any(@p0))

The array functions (cardinality, array_length, array_position, ...) and the @>/&& operators are documented in Scalar functions. Two names are refused at preparation even on PostgreSQL: array_reverse (no release provides it) and array_sort below an explicit 18+. Other providers reject the array surface with NotSupportedException.

Range types

PostgreSQL is the only supported provider with native range types (int4range, int8range, numrange, tsrange, tstzrange, daterange). Use the provider-agnostic Range<T> for a column or parameter; the provider binds it through Npgsql and reads it back preserving unbounded sides and the empty range. The PostgreSQL-only range surface (overlaps, range_contains/range_contained_by, range_union, lower/upper/isempty, the constructors and empty_range<T>()) lives on Postgres and is gated by SupportsRanges:

var window = new Range<int>(15, 25);   // [15,25)

ctx.From<IReservation>()
    .Where(e => SqlFunctions.Postgres.overlaps(e.During, window))
    .Select(e => e.Id)
    .ToList();                          // (during && @p0)

Multirange types (int4multirange…datemultirange) map to Range<T>[] and use the same operator names overloaded for multiranges, plus multirange, range_merge and the range_agg/range_intersect_agg aggregates. See PostgreSQL-specific SQL for the full surface.

The in-memory provider evaluates the whole range and multirange surface with the same semantics; every other provider rejects it with NotSupportedException. See PostgreSQL-specific SQL for the full table.

JSON and JSONB

PostgreSQL is the only supported provider with json/jsonb. A [JsonColumn] property maps a CLR object to a native jsonb column on read and write (Auto storage; Native forces jsonb, Text forces text), so the object round-trips through insert, update and RETURNING. Passing a JsonDocument, JsonElement or JsonNode parameter binds it as jsonb, so the access operators and functions work directly:

using System.Text.Json;

var document = JsonDocument.Parse("""{"name":"Alice","tags":["a","b"]}""");

using var ctx = new PostgresDataContext(connectionString, new DataContextBuilder());
ctx.From<IComplexEntity>()
    .Where(e => SqlFunctions.Postgres.json_get_text(SqlFunctions.Parameter<JsonDocument>(0), "name") == "Alice")
    .Select(e => e.Id)
    .ToList(document);

ctx.From<IComplexEntity>()
    .Select(e => SqlFunctions.Postgres.jsonb_agg(e.String));   // jsonb_agg(somestring)

Provider parameter binding: a plain JSON string parameter is bound as text. The six json_get/json_get_text/json_get_path/json_get_path_text accessors (string-key and integer-index overloads) are translator-local — they apply contextual SQL typing to a raw client document value (literal, captured value or explicit parameter) and parse it as jsonb in the generated SQL, and they type the key/index/path operand as text/integer/text[]. A native json/jsonb column or a server-side JSON expression is emitted uncast, so its operand and result types are unchanged. Use SqlFunctions.Postgres.json_cast(value) to parse a plain string for any other JSON function. A bare JsonNode/JsonNode? property (declared exactly JsonNode, not JsonObject/JsonArray) reads a native json/jsonb column: the column text is read and parsed for object, array and scalar roots, in both scalar (Select(x => x.Data)) and composite projections. A SQL NULL and a JSON literal null both materialize as CLR null. Declared JsonObject/JsonArray properties and a bare JsonDocument/JsonElement scalar projection are outside this support (map them with [JsonColumn], or project a JsonDocument/JsonElement in a named shape). [JsonColumn] mapping and JSON parameter binding are unchanged, and nextorm generates no column DDL. The full surface (json_agg, jsonb_build_object, ->, ->>, #>, @>, ?, ?|, ?&, ...) is documented in Scalar functions. Other providers reject it with NotSupportedException.

SqlFunctions.Sql.base64_encode and base64_decode use the core encode(value, 'base64') / decode(value, 'base64') pair (the encode result has its trailing newline removed explicitly). SqlFunctions.Sql.crypt_gen_random calls gen_random_bytes from the pgcrypto extension: the extension must be installed on the server (CREATE EXTENSION pgcrypto). nextorm neither installs it nor detects its absence — a missing extension surfaces as the provider's native error.

Additional function surface

PostgreSQL also opts into greatest/least, date_trunc, the string_agg/array_agg aggregates, the aggregate FILTER (WHERE ...) clause, the portable iif (rendered case when ... then ... else ... end), the percent_rank/cume_dist/nth_value window functions and the built-in set-returning table functions generate_series, unnest, regexp_matches, regexp_split_to_table, jsonb_array_elements(_text), jsonb_each(_text), jsonb_object_keys, jsonb_path_query, ts_stat and the record functions jsonb_to_record/jsonb_to_recordset (SqlFunctions.Postgres.jsonb_to_record<TRow>(json), whose result schema is rendered as the alias column-definition list from the caller's TRow under SupportsResultSchema(TableFunctionSchema)):

ctx.From<IComplexEntity>()
    .GroupBy(e => new { e.Int })
    .Select(e => new
    {
        e.Int,
        Names = SqlFunctions.Sql.string_agg(e.String, ","),
        Big = SqlFunctions.Sql.count(() => e.Id > 10L)
    });   // string_agg(somestring, ',') ... count(*) filter (where (id > 10))

These are documented in Scalar functions. SQLite also accepts the FILTER clause; the other functions are PostgreSQL-only.

`*ALL` set operations and null ordering

PostgreSQL is the only supported relational provider that implements INTERSECT ALL and EXCEPT ALL, so IntersectAll/ExceptAll render their SQL directly.

var q = a.Select(x => x.Id).IntersectAll(b.Select(x => x.Id));   // ... intersect all ...

PostgreSQL treats NULL as the largest value, so ORDER BY … DESC puts the NULL group first. The shared test suite avoids depending on this; the provider test pins it explicitly.

var r = ctx.From<IComplexEntity>()
    .OrderByDescending(it => it.Int)
    .Select(it => new { it.Id })
    .ToList();
// r[0].Id == 1 (the row whose Int is NULL)

Explicit NULL placement

PostgreSQL has native NULLS FIRST/NULLS LAST, so NullsPosition First/Last render the modifier directly and ISqlDialect.SupportsNullsOrdering is true:

var r = ctx.From<IComplexEntity>()
    .OrderBy(it => it.Int, NullsPosition.Last)
    .Select(it => new { it.Id })
    .ToList();
// order by nullableint NULLS LAST

Placement is independent of direction: First/Last keep their group even when the key is descending (order by nullableint desc NULLS FIRST). NullsPosition.Default emits no clause and keeps the PostgreSQL default, which is direction-aware and opposite to SQLite/SQL Server/MySQL/MariaDB: ascending puts nulls last and descending puts them first (null sorts as the largest value). Because the modifier is native, the key is emitted once, DISTINCT/DISTINCT ON need no special handling, and a parameterized key binds once. See Sorting and paging.

Large objects (LOB streaming)

A single byte[] or string column can be read as a Stream/TextReader without loading the value into managed memory. Npgsql implements GetStream/GetTextReader for bytea and text, so PostgreSQL is one of the three providers that support the LOB terminals in this release (alongside SQL Server and SQLite):

await using var stream = ctx.From<BinaryEntity>()
    .Where(x => x.Id == 1)
    .Select(x => x.Payload)
    .ToStream();          // opens the reader with CommandBehavior.SequentialAccess

using var reader = ctx.From<Document>()
    .Where(x => x.Id == 1)
    .Select(x => x.Body)
    .ToTextReader();

The returned stream owns the reader and the per-call command until it is disposed, and it does not close the context; the projection must be exactly one byte[]/string column (otherwise InvalidOperationException). MySQL/MariaDB, ClickHouse and the in-memory provider reject the terminals with NotSupportedException. See Streaming large objects.

PostgreSQL also supports the multi-column ToDataReader/ToDataReaderAsync terminal: it hands the same sequential-access command over as a caller-owned DbDataReader, so the caller can read every column and row (or several LOB columns in order) without materialising the result. SQLite supports it too, but via a buffered, locator-free reader (no rowid, no SequentialAccess, so no chunked LOB); MySQL/MariaDB, ClickHouse and the in-memory provider have no sequential-access support and reject it.

Aliases

Derived tables and table-valued functions must be aliased with double quotes:

select t1.value, t2.somestring as "String"
 from all_rows() as "t1"
 join complex_entity as "t2" on t1.id = t2.id

Bulk insert

The native path is binary COPY ... FROM STDIN (FORMAT BINARY) through NpgsqlBinaryImporter on the context's own connection; Auto selects it for a compatible request, Native refuses by name instead of falling back, and Portable writes INSERT ... VALUES (the only path for Returning*).

Rows stream into one importer; a finite MaxBatchSize starts one importer per bounded chunk, Completed on success and disposed on error or cancellation.
KeepIdentity keeps the explicit identity column in the COPY column list; the portable statement adds OVERRIDING SYSTEM VALUE.
IgnoreDuplicates is portable-only (ON CONFLICT DO NOTHING). TableLock, FireTriggers and CheckConstraints=false are refused by name; KeepNulls=false needs a default-equivalent shape (every written column nullable with a NULL/absent SQL default).
DatabaseName addresses only the connection's current database — naming it is a no-op, another database is refused by name; ServerName is refused (no rerouting).
Timeout is one whole-invocation budget feeding the importer timeout and the linked token; an expired budget is a TimeoutException, and a caller cancellation always wins.

See Bulk insert.

Provider differences

Aspect	PostgreSQL
Parameter placeholder	`@name`
Paging	`limit n` / `limit n offset m` / `offset m`
Injected sort when paging	none
Concat	`\|\|`
Coalesce	`coalesce`
Boolean literal	`true` / `false`
Identifier quoting	double quotes (`as "t1"`)
Derived table / TVF alias	required
`*ALL`	supported
Arrays	supported (`any(@array)`, `cardinality`, ...)
JSON/JSONB	supported (`json_agg`, `->`, ...; native `json`/`jsonb` columns via `[JsonColumn]`; `JsonDocument`/`JsonElement`/`JsonNode` params bind as `jsonb`)
LOB streaming (`ToStream`/`ToTextReader`, `ToDataReader`)	supported (`SequentialAccess`; single `byte[]`/`string` column, or a multi-column caller-owned reader)
`greatest` / `least` / `date_trunc`	supported (`greatest`/`least` ignore NULL arguments)
Conditional function	`iif(cond, a, b)` → `case when cond then a else b end`
Window functions	`percent_rank()`, `cume_dist()`, `nth_value(expr, n)` supported
`date_add` / `end_of_month` / `DateTime.Add*`	interval arithmetic (`x + (n * interval '1 day')`)
`string_agg` / `array_agg` / aggregate `filter`	supported (aggregate `filter` requires 9.4+; unset assumes ≥9.4)
Median / percentiles	`SqlFunctions.Postgres.median(() => x)` → `percentile_cont(0.5) within group (order by x)` (aggregate, one row per group; floating-point sort only); `percentile_cont`/`percentile_disc` ordered-set; generic `percentile` throws (`SupportsOrderedPercentiles`)
Server version	configured explicitly via the context `Version`
Session/info functions	`current_user`, `session_user`, `current_schema`, `current_database()`, `version()`
Table functions	`generate_series`, `unnest`, `regexp_matches`, `regexp_split_to_table`, `jsonb_array_elements(_text)`, `jsonb_each(_text)`, `jsonb_object_keys`, `jsonb_path_query`, `ts_stat`
Recursive CTE	`with recursive` (no max-recursion option)
`stdev` / `stdevp`	`stddev` / `stddev_pop`
`var` / `varp`	`variance` / `var_pop`
`DateTime.Now` / `UtcNow`	`now()` / `now() at time zone 'utc'`
`ORDER BY` null placement (default)	nulls sort as the largest value, so `DESC` puts them first and `ASC` last
Explicit NULL placement (`NullsPosition`)	native `NULLS FIRST`/`NULLS LAST` (`SupportsNullsOrdering`)

Table of Contents

PostgreSQL provider

Overview

Registering the provider

Server version

Paging

Coalesce, date parts and aggregates

Arrays

Range types

JSON and JSONB

Additional function surface

`*ALL` set operations and null ordering

Explicit NULL placement

Large objects (LOB streaming)

Aliases

Bulk insert

Provider differences

See also

Table of Contents

PostgreSQL provider

Overview

Registering the provider

Server version

Paging

Coalesce, date parts and aggregates

Arrays

Range types

JSON and JSONB

Additional function surface

*ALL set operations and null ordering

Explicit NULL placement

Large objects (LOB streaming)

Aliases

Bulk insert

Provider differences

See also

`*ALL` set operations and null ordering