Table of Contents

Class PostgresDialect

Namespace
NextORM.Postgres
Assembly
nextorm.postgres.dll

PostgreSQL dialect: || concatenation, @name parameters, double-quoted identifiers, limit/offset paging and the aggregate name mapping (stdev -> stddev, ...).

public class PostgresDialect : SqlDialectBase, ISqlDialect
Inheritance
PostgresDialect
Implements
Inherited Members
Extension Methods

Remarks

The type is deliberately not sealed. Subclassing it and overriding ExtremeRowRenderer is the supported external extension boundary (a public compatibility commitment), so an assembly without InternalsVisibleTo can install its own public IExtremeRowRenderer. The built-in renderer is internal and is not part of that contract. The public constructors and the shared Instance are preserved; returning null from an override keeps the portable window-function lowering.

Constructors

PostgresDialect()

Creates an unversioned PostgreSQL dialect: every supported form (including the ANSI aggregate FILTER clause) is emitted. The version-gated functions of PostgreSQL 18 (array_sort, uuidv7) are refused, and array_reverse is always refused because no current PostgreSQL release provides it. Equivalent to the shared Instance.

public PostgresDialect()

Remarks

The constructor is public so an external assembly can derive a custom dialect from PostgresDialect.

PostgresDialect(Version?)

Creates a PostgreSQL dialect for serverVersion. A version below 9.4 gates off the ANSI aggregate FILTER clause; array_sort and uuidv7 render only from an explicit 18. A null version is the unversioned form, which refuses those two and array_reverse (never available).

public PostgresDialect(Version? serverVersion)

Parameters

serverVersion Version

The PostgreSQL server version, or null for unset.

Remarks

The constructor is public so an external assembly can derive a custom dialect from PostgresDialect; passing null equals the parameterless form.

Fields

Instance

Gets the shared PostgreSQL dialect instance. Subclassing the dialect does not change this instance; a derived dialect is created through the public constructors instead.

public static readonly PostgresDialect Instance

Field Value

PostgresDialect

Properties

AggregateFilterStyle

How the provider attaches a filter to an aggregate. The safe default is None, which rejects the filtering overloads; PostgreSQL and SQLite return AnsiFilter and ClickHouse returns IfCombinator. Declared as a default interface method so that existing external implementations keep compiling.

public override AggregateFilterStyle AggregateFilterStyle { get; }

Property Value

AggregateFilterStyle

ConcatStringOperator

String concatenation operator, e.g. + or ||.

public override string ConcatStringOperator { get; }

Property Value

string

DeleteJoinRequiresUsing

PostgreSQL renders the USING spelling of MakeDeleteJoin(string, string, string, string, string, string?, KeywordCase).

public override bool DeleteJoinRequiresUsing { get; }

Property Value

bool

DistinctOn

PostgreSQL supports the SELECT DISTINCT ON (expr, ...) modifier.

public override IDistinctOnRenderer DistinctOn { get; }

Property Value

IDistinctOnRenderer

ExtremeRowRenderer

PostgreSQL renders eligible SelectWhereMax/SelectWhereMin with DISTINCT ON/ORDER BY ... LIMIT 1; the rest falls back to the portable window function. The property is virtual so a derived dialect can override it with a custom IExtremeRowRenderer; returning null keeps the portable window-function lowering. The built-in renderer is internal and is not a public contract.

public override IExtremeRowRenderer? ExtremeRowRenderer { get; }

Property Value

IExtremeRowRenderer

Iif

PostgreSQL has no iif/if function; it renders the conditional as a CASE expression.

public override IIifRenderer Iif { get; }

Property Value

IIifRenderer

Lock

PostgreSQL supports the trailing FOR UPDATE/FOR SHARE row-locking clause.

public override ILockRenderer Lock { get; }

Property Value

ILockRenderer

NullSafeComparison

PostgreSQL renders both null-safe comparison forms natively (IS [NOT] DISTINCT FROM).

public override INullSafeComparisonRenderer? NullSafeComparison { get; }

Property Value

INullSafeComparisonRenderer

RequireSubqueryAlias

True for providers that require a derived table (subquery in FROM) to have an alias.

public override bool RequireSubqueryAlias { get; }

Property Value

bool

ScalarFunctions

PostgreSQL renders every cross-provider scalar function natively (space as repeat(' ', n)).

public override IScalarFunctions ScalarFunctions { get; }

Property Value

IScalarFunctions

SessionInfoFunctions

PostgreSQL renders the whole session/information family.

public override ISessionInfoFunctions SessionInfoFunctions { get; }

Property Value

ISessionInfoFunctions

StringFormats

PostgreSQL renders CLR format specifiers through to_char/to_hex.

public override IStringFormatFunctions? StringFormats { get; }

Property Value

IStringFormatFunctions

SupportsApply

True when the provider can render CROSS APPLY/OUTER APPLY or their lateral equivalent (CROSS JOIN LATERAL / LEFT JOIN LATERAL ... ON true). SQLite has no lateral source, so the safe default is false.

public override bool SupportsApply { get; }

Property Value

bool

SupportsArrays

True when the provider has an array type and can render the array surface: array-typed parameters used with the any/all quantifiers (column = any(@array)) and the array functions in CommonFunctions. The safe default is false; only PostgreSQL opts in today.

public override bool SupportsArrays { get; }

Property Value

bool

SupportsBase64Decode

PostgreSQL decodes base64 through the core decode function.

public override bool SupportsBase64Decode { get; }

Property Value

bool

SupportsBase64Encode

PostgreSQL encodes base64 through the core encode function.

public override bool SupportsBase64Encode { get; }

Property Value

bool

SupportsBatch

PostgreSQL batches through NpgsqlBatch, which wraps the commands in an implicit transaction so they share one backend.

public override bool SupportsBatch { get; }

Property Value

bool

SupportsBitAggregates

True when the provider can render the bitwise aggregates bit_and, bit_or and bit_xor. The safe default is false; PostgreSQL and MySQL/MariaDB support them, but only PostgreSQL opts in today.

public override bool SupportsBitAggregates { get; }

Property Value

bool

SupportsBooleanAggregates

True when the provider can render the boolean aggregates bool_and, bool_or and every. The safe default is false; only PostgreSQL opts in today.

public override bool SupportsBooleanAggregates { get; }

Property Value

bool

SupportsBulkCopy

PostgreSQL has a native bulk path (COPY ... FROM STDIN (FORMAT BINARY)).

public override bool SupportsBulkCopy { get; }

Property Value

bool

SupportsCollation

PostgreSQL supports a per-expression COLLATE clause.

public override bool SupportsCollation { get; }

Property Value

bool

SupportsColumnDefault

PostgreSQL accepts DEFAULT as a value in the VALUES list.

public override bool SupportsColumnDefault { get; }

Property Value

bool

SupportsCreateTableAsSelect

PostgreSQL supports CREATE [TEMPORARY] TABLE ... AS SELECT.

public override bool SupportsCreateTableAsSelect { get; }

Property Value

bool

SupportsCreateTableAsSelectColumnList

PostgreSQL accepts a column list on CREATE TABLE ... AS SELECT.

public override bool SupportsCreateTableAsSelectColumnList { get; }

Property Value

bool

SupportsCreateTableAsSelectIfNotExists

PostgreSQL accepts IF NOT EXISTS on CREATE TABLE ... AS SELECT.

public override bool SupportsCreateTableAsSelectIfNotExists { get; }

Property Value

bool

SupportsCreateTableAsSelectOnCommit

PostgreSQL supports ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } on a temporary table.

public override bool SupportsCreateTableAsSelectOnCommit { get; }

Property Value

bool

SupportsCreateTableAsSelectWithNoData

PostgreSQL supports WITH [NO] DATA on CREATE TABLE ... AS SELECT.

public override bool SupportsCreateTableAsSelectWithNoData { get; }

Property Value

bool

SupportsCryptGenRandom

PostgreSQL generates random bytes through pgcrypto's gen_random_bytes.

public override bool SupportsCryptGenRandom { get; }

Property Value

bool

SupportsCryptoFunctions

PostgreSQL renders the digest (pgcrypto) and sha256 (core) hash functions.

public override bool SupportsCryptoFunctions { get; }

Property Value

bool

SupportsCube

True when the provider can render GROUP BY CUBE (...). The safe default is false; MySQL/MariaDB have no CUBE, so they leave it off.

public override bool SupportsCube { get; }

Property Value

bool

SupportsDataModifyingCtes

PostgreSQL allows a data-modifying statement (INSERT ... RETURNING) as a CTE body.

public override bool SupportsDataModifyingCtes { get; }

Property Value

bool

SupportsDateArithmetic

True when the provider can render the date arithmetic surface of CommonFunctions (date_add and end_of_month, also used for the DateTime.AddYears/ AddMonths/AddDays/... methods). The safe default is false; PostgreSQL, SQL Server and ClickHouse opt in today.

public override bool SupportsDateArithmetic { get; }

Property Value

bool

SupportsDateTimeOffsetTranslation

PostgreSQL stores a DateTimeOffset as timestamptz, so DTO operations are supported.

public override bool SupportsDateTimeOffsetTranslation { get; }

Property Value

bool

SupportsDateTrunc

True when the provider can render date_trunc with a date part name. The safe default is false; PostgreSQL and SQL Server 2022+ (datetrunc) opt in today.

public override bool SupportsDateTrunc { get; }

Property Value

bool

SupportsDefaultValues

PostgreSQL supports INSERT ... DEFAULT VALUES for an all-defaults row.

public override bool SupportsDefaultValues { get; }

Property Value

bool

SupportsDeleteJoin

PostgreSQL deletes rows based on a join through the USING clause.

public override bool SupportsDeleteJoin { get; }

Property Value

bool

SupportsDeleteJoinReturning

PostgreSQL returns the affected rows of a multi-table DELETE ... USING ... JOIN through RETURNING.

public override bool SupportsDeleteJoinReturning { get; }

Property Value

bool

SupportsExtendedScalarFunctions

True when the provider can render the extended scalar function library (SqlFunctions.Sql math/string/date/regular-expression and num_nulls/num_nonnulls helpers such as asin, split_part, lpad, regexp_replace, make_date, to_char). The safe default is false; only PostgreSQL opts in today.

public override bool SupportsExtendedScalarFunctions { get; }

Property Value

bool

SupportsFullText

True when the provider can render the full-text predicates contains and freetext (contains<T>(T?, string?)). The safe default is false; SQL Server opts in.

public override bool SupportsFullText { get; }

Property Value

bool

SupportsGreatestLeast

True when the provider can render greatest(...)/least(...). The safe default is false; PostgreSQL, MySQL/MariaDB, ClickHouse, SQL Server 2022+ and SQLite (through the scalar max/min) opt in.

public override bool SupportsGreatestLeast { get; }

Property Value

bool

SupportsGroupingSets

True when the provider can render an explicit GROUP BY GROUPING SETS (...) list. The safe default is false; MySQL/MariaDB have no grouping sets, so they leave it off.

public override bool SupportsGroupingSets { get; }

Property Value

bool

SupportsIdentityFunction

PostgreSQL reads the last generated identity of the session through the lastval() function.

public override bool SupportsIdentityFunction { get; }

Property Value

bool

SupportsInlineHints

PostgreSQL renders join/subquery/tables-in-scope hints as one /*+ ... */ comment.

public override bool SupportsInlineHints { get; }

Property Value

bool

SupportsIntersectExceptAll

True when the provider can render the * ALL variants of INTERSECT and EXCEPT (intersect all / except all). UNION ALL is not covered by this flag because it is universally supported. SQL Server does not support either variant, and SQLite has no intersect all/except all at all, so the safe default is false.

public override bool SupportsIntersectExceptAll { get; }

Property Value

bool

SupportsJson

True when the provider has a native JSON storage type and can store a JsonColumnAttribute as it (rather than as text): the Auto/Native storage forms resolve to NativeJsonProviderType when this is true. The safe default is false; PostgreSQL and ClickHouse opt in today. This flag does not gate the PostgreSQL json/jsonb functions and operators — that surface is SupportsPostgresJsonSql.

public override bool SupportsJson { get; }

Property Value

bool

SupportsMergeConditionalBranches

PostgreSQL MERGE supports an explicit ON <condition> and WHEN ... AND <condition>.

public override bool SupportsMergeConditionalBranches { get; }

Property Value

bool

SupportsMergeDelete

PostgreSQL MERGE supports a WHEN MATCHED THEN DELETE branch.

public override bool SupportsMergeDelete { get; }

Property Value

bool

SupportsMergeDoNothing

PostgreSQL MERGE supports a THEN DO NOTHING branch.

public override bool SupportsMergeDoNothing { get; }

Property Value

bool

SupportsMergeStatement

PostgreSQL 15+ renders a general MERGE; the server version is a documented requirement.

public override bool SupportsMergeStatement { get; }

Property Value

bool

SupportsMergeTargetQualification

PostgreSQL MERGE forbids qualifying a target column in the UPDATE SET list.

public override bool SupportsMergeTargetQualification { get; }

Property Value

bool

SupportsNamedWindows

PostgreSQL declares named windows (WINDOW w AS (...)) and references them with OVER w.

public override bool SupportsNamedWindows { get; }

Property Value

bool

SupportsNativeDuration

PostgreSQL has a native duration type (interval), so a TimeSpan is stored natively.

public override bool SupportsNativeDuration { get; }

Property Value

bool

SupportsNthValue

PostgreSQL supports nth_value(value, n) as a window function.

public override bool SupportsNthValue { get; }

Property Value

bool

SupportsNullsOrdering

PostgreSQL renders the native NULLS FIRST/NULLS LAST ordering modifier.

public override bool SupportsNullsOrdering { get; }

Property Value

bool

SupportsOnConflict

PostgreSQL expresses a key upsert as INSERT ... ON CONFLICT (<keys>) DO UPDATE SET ... (9.5+).

public override bool SupportsOnConflict { get; }

Property Value

bool

SupportsOnConflictDoNothing

PostgreSQL skips conflicting rows with a trailing ON CONFLICT DO NOTHING.

public override bool SupportsOnConflictDoNothing { get; }

Property Value

bool

SupportsOrderedAggregates

True when the provider can render the ordered-set aggregates percentile_cont/percentile_disc/mode with WITHIN GROUP (ORDER BY ...). The safe default is false; only PostgreSQL opts in today.

public override bool SupportsOrderedAggregates { get; }

Property Value

bool

SupportsOrderedPercentiles

Defaults to false; PostgreSQL opts into the ordered-set percentile/median form.

public override bool SupportsOrderedPercentiles { get; }

Property Value

bool

SupportsOrdinalComparison

PostgreSQL can express ordinal comparison through the "C" collation.

public override bool SupportsOrdinalComparison { get; }

Property Value

bool

SupportsPercentRankCumeDist

PostgreSQL supports the ANSI percent_rank/cume_dist window functions.

public override bool SupportsPercentRankCumeDist { get; }

Property Value

bool

SupportsPostgresJsonSql

Defaults to false; only PostgreSQL renders the JSON functions/operators surface.

public override bool SupportsPostgresJsonSql { get; }

Property Value

bool

SupportsQueryHints

PostgreSQL has no statement hints in the core server; the optional pg_hint_plan extension reads an inline /*+ ... */ comment. nextorm renders that comment unconditionally, because on a server without the extension it is an ordinary comment.

public override bool SupportsQueryHints { get; }

Property Value

bool

SupportsRandomSeed

PostgreSQL has the standalone session random seed setseed.

public override bool SupportsRandomSeed { get; }

Property Value

bool

SupportsRangeColumns

True when the provider can store a Range<T> as a pair of scalar columns declared with RangeColumnsAttribute; false by default. See SupportsRangeColumns.

public override bool SupportsRangeColumns { get; }

Property Value

bool

SupportsRanges

True when the provider has a native range type and can render the range surface: the Range<T> constructors/functions/operators on PostgresFunctions (overlaps, range_contains/range_contained_by, range_union, …). The safe default is false; only PostgreSQL opts in today. Declared as a default interface method so existing external implementations keep compiling.

public override bool SupportsRanges { get; }

Property Value

bool

SupportsRawSqlSource

PostgreSQL supports a raw SQL derived table (FROM (<sql>) AS alias).

public override bool SupportsRawSqlSource { get; }

Property Value

bool

SupportsRegex

PostgreSQL matches with the ~/~* operators and replaces with regexp_replace.

public override bool SupportsRegex { get; }

Property Value

bool

SupportsRegressionAggregates

True when the provider can render the regression aggregates of the regr_ family (regr_slope, regr_intercept, regr_r2, regr_count, regr_avgx, regr_avgy). The safe default is false; only PostgreSQL opts in today. It is split from SupportsStatisticalAggregates so a provider that has corr/covar_ but no regr_* (ClickHouse) can opt into one without the other.

public override bool SupportsRegressionAggregates { get; }

Property Value

bool

SupportsReturning

PostgreSQL supports INSERT ... RETURNING <column>.

public override bool SupportsReturning { get; }

Property Value

bool

SupportsRollup

True when the provider can render GROUP BY ROLLUP (...). The safe default is false; SQL Server, PostgreSQL, SQLite, MySQL/MariaDB and ClickHouse opt in (the latter group spells it ... WITH ROLLUP).

public override bool SupportsRollup { get; }

Property Value

bool

SupportsSavepoints

PostgreSQL supports SAVEPOINT/ROLLBACK TO SAVEPOINT/RELEASE SAVEPOINT.

public override bool SupportsSavepoints { get; }

Property Value

bool

SupportsSelectWhereMinMax

PostgreSQL supports the SelectWhereMax/SelectWhereMin feature (native or portable).

public override bool SupportsSelectWhereMinMax { get; }

Property Value

bool

SupportsSequentialAccess

Npgsql exposes streaming GetStream/GetTextReader accessors for LOB columns.

public override bool SupportsSequentialAccess { get; }

Property Value

bool

SupportsStatisticalAggregates

True when the provider can render the statistical aggregates corr, covar_pop and covar_samp. The safe default is false; PostgreSQL and ClickHouse opt in today.

public override bool SupportsStatisticalAggregates { get; }

Property Value

bool

SupportsStoredProcedures

Npgsql maps CommandType.StoredProcedure to CALL name(...), so PostgreSQL procedures (not functions) are invoked through ExecuteProcedure.

public override bool SupportsStoredProcedures { get; }

Property Value

bool

SupportsStringArrayAggregates

True when the provider can render the string_agg/array_agg aggregate surface. The safe default is false; only PostgreSQL opts in today.

This is the umbrella capability: a dialect that opts into both aggregates sets it, and SupportsStringAgg/SupportsArrayAgg default to its value. A provider that supports only one of the two (SQL Server has string_agg but no array type) overrides the individual flag instead. 

public override bool SupportsStringArrayAggregates { get; }

Property Value

bool

SupportsSubQueryHints

PostgreSQL expresses the subquery hint through the same /*+ ... */ comment.

public override bool SupportsSubQueryHints { get; }

Property Value

bool

SupportsTableValuedParameters

PostgreSQL emulates a table-valued parameter with a typed array (unnest/ANY) or a JSON document (jsonb_to_recordset).

public override bool SupportsTableValuedParameters { get; }

Property Value

bool

SupportsTextSearchFunctions

PostgreSQL is the only provider with the native tsvector/tsquery text-search surface.

public override bool SupportsTextSearchFunctions { get; }

Property Value

bool

SupportsTruncate

PostgreSQL has a native TRUNCATE TABLE.

public override bool SupportsTruncate { get; }

Property Value

bool

SupportsUpdateJoin

PostgreSQL updates rows based on a join through the FROM clause.

public override bool SupportsUpdateJoin { get; }

Property Value

bool

SupportsUpdateJoinReturning

PostgreSQL returns the affected rows of a multi-table UPDATE ... FROM ... JOIN through RETURNING.

public override bool SupportsUpdateJoinReturning { get; }

Property Value

bool

SupportsWindowFrameExclusion

PostgreSQL supports the frame EXCLUDE CURRENT ROW/GROUP/TIES/NO OTHERS clause.

public override bool SupportsWindowFrameExclusion { get; }

Property Value

bool

SupportsWindowFrameGroups

PostgreSQL 11+ supports the GROUPS window frame unit.

public override bool SupportsWindowFrameGroups { get; }

Property Value

bool

SupportsWithTies

PostgreSQL supports FETCH FIRST ... WITH TIES.

public override bool SupportsWithTies { get; }

Property Value

bool

TableSample

PostgreSQL supports the TABLESAMPLE table modifier (both SYSTEM and BERNOULLI).

public override ITableSampleMethods TableSample { get; }

Property Value

ITableSampleMethods

Tuple

PostgreSQL has a native row-value type: ROW(a, b) with (row).fN access.

public override ITupleRenderer? Tuple { get; }

Property Value

ITupleRenderer

UpdateJoinRequiresFrom

PostgreSQL renders the FROM spelling of the multi-table update (the target stays out of FROM).

public override bool UpdateJoinRequiresFrom { get; }

Property Value

bool

UuidGenerators

PostgreSQL renders both UUID generators through the core functions.

public override IUuidGenerators UuidGenerators { get; }

Property Value

IUuidGenerators

Methods

Escape(string)

Quotes an identifier (alias, keyword).

public override string Escape(string keyword)

Parameters

keyword string

Returns

string

MakeAggregate(string)

Maps an aggregate function name to the provider specific one, e.g. stdev -> stddev.

public override string MakeAggregate(string name)

Parameters

name string

Returns

string

MakeBase64Decode(string)

Defaults to the unsupported-provider exception; reached only when SupportsBase64Decode is true.

public override string MakeBase64Decode(string value)

Parameters

value string

Returns

string

MakeBase64Encode(string)

Defaults to the unsupported-provider exception; reached only when SupportsBase64Encode is true.

public override string MakeBase64Encode(string value)

Parameters

value string

Returns

string

MakeBool(bool)

Renders the SQL literal for a boolean value.

public override string MakeBool(bool v)

Parameters

v bool

Returns

string

MakeCoalesce(string, string)

Renders coalesce(v1, v2) over the already-rendered operands.

public override string MakeCoalesce(string v1, string v2)

Parameters

v1 string
v2 string

Returns

string

MakeCollate(string, string, KeywordCase)

Renders value COLLATE collation over the already-rendered value. collation is the provider-native collation name, quoted where the provider requires it (PostgreSQL). Only called when SupportsCollation is true.

public override string MakeCollate(string value, string collation, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

value string
collation string
keywordCase KeywordCase

Returns

string

MakeColumnReference(string)

Quotes a column alias when it is referenced from an outer query. Providers that emit quoted aliases (so they survive as case-sensitive identifiers) must quote the reference accordingly.

public override string MakeColumnReference(string name)

Parameters

name string

Returns

string

MakeCreateTableAsHead(CreateTableAsClause, KeywordCase)

Adds the PostgreSQL ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } clause of a temporary table.

protected override string MakeCreateTableAsHead(CreateTableAsClause clause, KeywordCase keywordCase)

Parameters

clause CreateTableAsClause
keywordCase KeywordCase

Returns

string

MakeCreateTableAsTail(CreateTableAsClause, KeywordCase)

Appends the PostgreSQL WITH NO DATA clause.

protected override string MakeCreateTableAsTail(CreateTableAsClause clause, KeywordCase keywordCase)

Parameters

clause CreateTableAsClause
keywordCase KeywordCase

Returns

string

MakeCryptGenRandom(string)

Defaults to the unsupported-provider exception; reached only when SupportsCryptGenRandom is true.

public override string MakeCryptGenRandom(string length)

Parameters

length string

Returns

string

MakeDateAdd(string, string, string, bool)

DTO date addition runs on the UTC wall time and is re-typed to timestamptz (a naive timestamp without time zone result cannot be read as a DateTimeOffset), so the emitted value is the original instant plus the interval, independent of the session time zone/DST.

public override string MakeDateAdd(string field, string amount, string value, bool isDateTimeOffset)

Parameters

field string
amount string
value string
isDateTimeOffset bool

Returns

string

MakeDateDiffBig(string, string, string)

The ANSI date_diff casts a sub-day span to integer; the 64-bit variant widens that cast to bigint so milliseconds/microseconds over a long range do not overflow.

public override string MakeDateDiffBig(string field, string start, string end)

Parameters

field string
start string
end string

Returns

string

MakeDatePart(string, string)

PostgreSQL's extract(epoch ...) returns numeric, so it is cast to double precision.

public override string MakeDatePart(string part, string value)

Parameters

part string
value string

Returns

string

MakeDatePart(string, string, bool)

DTO date parts are extracted from the UTC wall time (at time zone 'UTC') so the result does not depend on the session time zone or DST.

public override string MakeDatePart(string part, string value, bool isDateTimeOffset)

Parameters

part string
value string
isDateTimeOffset bool

Returns

string

MakeDateTimeOffsetNow(bool)

PostgreSQL's now() is a session-time-zone-independent instant; materialized as a timestamptz it canonicalises to an offset-zero DateTimeOffset.

public override string MakeDateTimeOffsetNow(bool utc)

Parameters

utc bool

Returns

string

MakeDeleteJoin(string, string, string, string, string, string?, KeywordCase)

Renders the PostgreSQL USING form of a multi-table delete. The joined tables are listed in USING and the join conditions are folded into the WHERE, because a USING join condition cannot reference the delete target (the target is aliased in DELETE FROM).

public override string MakeDeleteJoin(string target, string targetAlias, string fromAndJoins, string usingSources, string joinConditions, string? whereSql, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

target string
targetAlias string
fromAndJoins string
usingSources string
joinConditions string
whereSql string
keywordCase KeywordCase

Returns

string

MakeDurationType(DurationUnit?, int)

PostgreSQL's native duration type is interval, optionally with fractional-second precision.

public override string MakeDurationType(DurationUnit? unit, int precision = 0)

Parameters

unit DurationUnit?
precision int

Returns

string

MakeFullText(string, string, string)

Renders the full-text predicate functionName (contains/freetext) over the already-rendered column and search operands. Only called when SupportsFullText is true.

public override string MakeFullText(string functionName, string column, string search)

Parameters

functionName string
column string
search string

Returns

string

MakeIdentityFunction(KeywordCase)

Renders the identity-function query select lastval().

public override string MakeIdentityFunction(KeywordCase keywordCase = KeywordCase.Lower)

Parameters

keywordCase KeywordCase

Returns

string

MakeMathFunction(string, IReadOnlyList<string>)

PostgreSQL's log() is base 10; the natural logarithm (Math.Log) is ln(). The two-argument form only exists as log(numeric, numeric), so both operands are cast to numeric.

public override string MakeMathFunction(string name, IReadOnlyList<string> args)

Parameters

name string
args IReadOnlyList<string>

Returns

string

MakeMathFunction(string, IReadOnlyList<string>, IReadOnlyList<Type>)

PostgreSQL's two-argument round only accepts numeric, so a double precision/real first argument is cast; decimal/integer already resolve to round(numeric, integer).

public override string MakeMathFunction(string name, IReadOnlyList<string> args, IReadOnlyList<Type> argTypes)

Parameters

name string
args IReadOnlyList<string>
argTypes IReadOnlyList<Type>

Returns

string

MakeMergeReturning(IReadOnlyList<string>, KeywordCase)

PostgreSQL MERGE ... RETURNING must qualify target columns, otherwise they are ambiguous with the source.

public override string MakeMergeReturning(IReadOnlyList<string> columns, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

columns IReadOnlyList<string>
keywordCase KeywordCase

Returns

string

MakeOrdinal(string, bool)

Renders a string operand normalised for ordinal comparison. A provider with a per-expression binary collation emits value COLLATE <binary>; a provider whose native String order is already ordinal (ClickHouse) returns the value unchanged. ignoreCase additionally case-folds the operand. Only called when SupportsOrdinalComparison is true.

public override string MakeOrdinal(string value, bool ignoreCase)

Parameters

value string
ignoreCase bool

Returns

string

MakeOverridingSystemValue(KeywordCase)

PostgreSQL writes an explicit value to a GENERATED ALWAYS AS IDENTITY column with OVERRIDING SYSTEM VALUE.

public override string MakeOverridingSystemValue(KeywordCase keywordCase = KeywordCase.Lower)

Parameters

keywordCase KeywordCase

Returns

string

MakePage(Paging, StringBuilder, KeywordCase)

Appends the provider's paging clause (LIMIT/OFFSET, OFFSET/FETCH or TOP) for paging to sqlBuilder.

public override void MakePage(Paging paging, StringBuilder sqlBuilder, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

paging Paging
sqlBuilder StringBuilder
keywordCase KeywordCase

MakeParam(string)

Renders a parameter placeholder, e.g. @name or $name.

public override string MakeParam(string name)

Parameters

name string

Returns

string

MakeRegexMatch(string, string, bool)

Renders a regular-expression match predicate over the already-rendered value. pattern is the raw pattern text (the dialect renders and escapes the string literal itself) and ignoreCase selects IgnoreCase. Reached only when SupportsRegex is true.

public override string MakeRegexMatch(string value, string pattern, bool ignoreCase)

Parameters

value string
pattern string
ignoreCase bool

Returns

string

MakeRegexReplace(string, string, string, bool)

Renders a regular-expression replace over the already-rendered value, replacing every match as Replace(string, string, string) does. pattern and replacement are raw text (the dialect renders and escapes the literals). Reached only when SupportsRegex is true.

A replacement backreference uses the provider's group syntax (C# $1, SQL \1); it is not rewritten, so a replacement with a group reference is not portable as written. 

public override string MakeRegexReplace(string value, string pattern, string replacement, bool ignoreCase)

Parameters

value string
pattern string
replacement string
ignoreCase bool

Returns

string

MakeRepeat(string, string)

Renders value repeated count times. The primitive behind the new string(char, n) translation and MakePad(string, string, string, bool).

public override string MakeRepeat(string value, string count)

Parameters

value string
count string

Returns

string

MakeStringPosition(string, string)

Renders the one-based position of substring in value, or 0 when it is absent (charindex, instr, strpos, position).

protected override string MakeStringPosition(string value, string substring)

Parameters

value string
substring string

Returns

string

MakeStringReverse(string)

Renders a character-wise reversal of value.

protected override string MakeStringReverse(string value)

Parameters

value string

Returns

string

MakeStuff(string, string, string?, string)

Renders Remove(int)/Remove(int, int) and Insert(int, string) as one stuff/overlay-style expression over the already-rendered zero-based start. A count of null removes through the end; newValue is spliced in at start otherwise (the Insert translation passes the string "0" so nothing is removed).

public override string MakeStuff(string value, string start, string? count, string newValue)

Parameters

value string
start string
count string
newValue string

Returns

string

MakeTableFunctionAlias(string, string?, KeywordCase)

PostgreSQL's record set-returning functions take their result schema as an alias column-definition list: jsonb_to_record(json) as "t1"(a integer, b text).

public override string MakeTableFunctionAlias(string tableAlias, string? columnDefinitionList, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

tableAlias string
columnDefinitionList string
keywordCase KeywordCase

Returns

string

MakeTypeName(Type)

PostgreSQL's text type is text, its duration type is interval, a Range<T> maps to the native range type selected by its bound type and a Range<T> array maps to the matching multirange.

public override string MakeTypeName(Type type)

Parameters

type Type

The CLR type to name.

Returns

string

The PostgreSQL type name.

RenderOrderBy(string, OrderDirection, NullsPosition)

Renders one logical ORDER BY item with an explicit NULL placement. The default emulates the placement with a CASE rank key whose prefix is always ascending; providers that opt into SupportsNullsOrdering override it with the native form.

public override string RenderOrderBy(string expressionSql, OrderDirection direction, NullsPosition nullsPosition)

Parameters

expressionSql string

The already-qualified, already-quoted key SQL.

direction OrderDirection

The requested key direction.

nullsPosition NullsPosition

The explicit NULL placement.

Returns

string

The rendered ordering item.

RenderQueryHints(string, IReadOnlyList<string>, string?, KeywordCase)

Renders the statement-level hints as a /*+ ... */ comment immediately after the top-level select (the position pg_hint_plan reads; a WITH prefix and subqueries are skipped). PostgreSQL has no maxrecursion option, so maxRecursionOption is ignored.

public override string RenderQueryHints(string sql, IReadOnlyList<string> hints, string? maxRecursionOption, KeywordCase keywordCase = KeywordCase.Lower)

Parameters

sql string
hints IReadOnlyList<string>
maxRecursionOption string
keywordCase KeywordCase

Returns

string

SupportsArrayFunction(string)

Per-name availability of the PostgreSQL array functions: array_reverse is not available in any current PostgreSQL release, and array_sort arrived in PostgreSQL 18, so it needs an explicit 18+ version (an unset version refuses it). Every other name keeps the family default.

public override bool SupportsArrayFunction(string name)

Parameters

name string

The SQL name of the array function, without schema qualification.

Returns

bool

SupportsDatePart(string)

PostgreSQL renders every date part through extract, including the ISO week and dow forms.

public override bool SupportsDatePart(string part)

Parameters

part string

Returns

bool

SupportsResultSchema(TableFunctionSchema)

PostgreSQL renders the row-derived schema as the alias column-definition list of the record function.

public override bool SupportsResultSchema(TableFunctionSchema placement)

Parameters

placement TableFunctionSchema

Returns

bool

SupportsTableFunction(string)

True when the provider supports the built-in table-valued function name declared in CommonFunctions (for example generate_series, unnest, string_split, openjson, numbers/numbers_mt, zeros/zeros_mt). User-defined table-function factories (returning TvfEntityBuilder<T>) are not gated by this and are emitted verbatim on every provider.

public override bool SupportsTableFunction(string name)

Parameters

name string

Returns

bool

WrapTableFunction(string, string)

PostgreSQL names the only output column of a scalar set-returning function after the function, but aliasing the function in FROM renames that column to the alias. nextorm always aliases a derived source (RequireSubqueryAlias), so those functions are wrapped in a one-column subquery that restores the function-named column (select generate_series from generate_series(...)). Functions with an explicit output column (value, key/value, word/ndoc/nentry) keep their names and are emitted unchanged.

public override string WrapTableFunction(string name, string call)

Parameters

name string
call string

Returns

string

WrapTableFunction(string, string, IReadOnlyList<string>)

PostgreSQL renders the record/table-valued system-information readers of #281 with an explicit projection that keeps every documented column name while converting the types the row mapper cannot read: pg_lsn, oid, regclass and xid8 columns become text (the scalar family casts its oid/pg_lsn/xid8 results the same way) and the internal "char" keyword-category code becomes text. The argument casts are load-bearing for the same reason as in the scalar family: an oid, xid, pg_lsn or pg_snapshot argument does not resolve from a text parameter. A function whose columns and arguments are all mappable is left alone and keeps its bare call.

public override string WrapTableFunction(string name, string call, IReadOnlyList<string> arguments)

Parameters

name string
call string
arguments IReadOnlyList<string>

Returns

string
Edit this page