Class PostgreSqlDialect

Namespace

eQuantic.Core.Data.PostgreSql

Assembly

eQuantic.Core.Data.PostgreSql.dll

PostgreSQL's SQL flavour: snake_case naming, "quoted" identifiers, LIMIT/OFFSET, identity keys read back with RETURNING, native array columns (membership as = ANY(col), atomic append/remove), row-wise tuple comparisons, and scalar-keyed dictionaries as jsonb document columns (ContainsKey as the ? operator, indexer reads as ->>).

public sealed class PostgreSqlDialect : SqlDialect

Inheritance

object

SqlDialect

PostgreSqlDialect

Inherited Members

SqlDialect.Functions

SqlDialect.ColumnName(string)

SqlDialect.TableName(string)

SqlDialect.LimitClause(string, string)

SqlDialect.RequiresOrderByForLimit

SqlDialect.FalseLiteral

SqlDialect.SqlType(RelationalColumn)

SqlDialect.SizedSqlType(Type, int, int, int)

SqlDialect.NormalizeStoredType(string)

SqlDialect.CreateTableSql(string, string)

SqlDialect.CreateIndexSql(string, string, string, bool)

SqlDialect.RenameTableSql(string, string)

SqlDialect.DropTableSql(string)

SqlDialect.Literal(object)

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Constructors

PostgreSqlDialect()

Adds the PostgreSQL-specific function translations on top of the standard set.

public PostgreSqlDialect()

Properties

GeneratedKeyDdl

The DDL column suffix declaring a database-generated key (e.g. GENERATED BY DEFAULT AS IDENTITY).

public override string GeneratedKeyDdl { get; }

Property Value

string

IntrospectColumnsSql

A query listing every column of the schema in use, as four values in order: the table's name, the column's name, its type spelled the way this dialect spells it in DDL, and whether it accepts null. null when the dialect cannot read its own catalogue, which makes drift unanswerable rather than answered wrongly.

Each dialect writes its own, and composes the type in SQL rather than leaving it to be reassembled from parts afterwards. Catalogues disagree about where a type's facets live — one keeps the whole spelling in a column, another splits length from precision from fractional seconds — and the disagreement is far better settled by the store that knows the answer.

public override string? IntrospectColumnsSql { get; }

Property Value

string

Remarks

format_type is why this reads the catalogue directly instead of information_schema: it returns the type exactly as PostgreSQL writes it, facets and array brackets included, which is the one spelling a declared type can be compared against without reassembling it from parts.

SupportsBulkInsert

Whether the engine has a native bulk-load path for this dialect (PostgreSQL COPY, SQL Server SqlBulkCopy, MySQL's bulk loader). BulkInsertAsync refuses on dialects that answer false rather than quietly running an ordinary batch — asking for a bulk load and getting row-by-row inserts is exactly the kind of silent cost this engine does not ship.

public override bool SupportsBulkInsert { get; }

Property Value

bool

System

The OpenTelemetry db.system value (e.g. postgresql, mysql, mssql).

public override string System { get; }

Property Value

string

Methods

AddColumnSql(string, string, string)

The DDL adding a column to an existing table (added nullable, matching create-table semantics).

public override string AddColumnSql(string quotedTable, string quotedColumn, string sqlType)

Parameters

quotedTable string

The quoted table.

quotedColumn string

The quoted column.

sqlType string

The column's SQL type.

Returns

string

AlterColumnType(string, string, string)

The DDL altering a column's type (a dialect may need a cast clause).

public override string AlterColumnType(string quotedTable, string quotedColumn, string sqlType)

Parameters

quotedTable string

quotedColumn string

sqlType string

Returns

string

BindValue(object?)

Converts a value before binding (a dialect may need CLR-side coercion, e.g. arrays); identity by default.

public override object? BindValue(object? value)

Parameters

value object

Returns

object

BulkInsertAsync(DbConnection, DbTransaction?, string, IReadOnlyList<RelationalColumn>, IReadOnlyList<object?[]>, CancellationToken)

Bulk-loads rows through the store's native mechanism. Implemented by the dialects that declare SupportsBulkInsert; each row carries the column values in columns order, already converted to their stored form by the engine.

public override Task<long> BulkInsertAsync(DbConnection connection, DbTransaction? transaction, string quotedTable, IReadOnlyList<RelationalColumn> columns, IReadOnlyList<object?[]> rows, CancellationToken cancellationToken)

Parameters

connection DbConnection

The open connection (inside the caller's transaction, when there is one).

transaction DbTransaction

The ambient transaction, or null.

quotedTable string

The quoted target table.

columns IReadOnlyList<RelationalColumn>

The target columns, in the order each row's values arrive.

rows IReadOnlyList<object[]>

The rows to load.

cancellationToken CancellationToken

The cancellation token.

Returns

Task<long>

The number of rows loaded.

Remarks

PostgreSQL's binary COPY — the fastest load path the server offers: rows stream in the wire format with no per-row statement, parse or plan. Npgsql infers each value's type from the CLR value, which is why the engine hands over already-converted stored values.

CollectionContains(string, string, bool)

Renders a collection-membership test (member CONTAINS value / CONTAINS KEY) for stores with native collection columns; the base dialect cannot express it (the clause degrades to residual).

public override string CollectionContains(string column, string parameter, bool key)

Parameters

column string

The quoted column.

parameter string

The bound value's parameter marker.

key bool

Whether the test targets a map key.

Returns

string

CollectionMutation(string, string, bool, bool)

Renders a collection-mutating SET fragment (col = col + items), for stores with collection columns.

public override string CollectionMutation(string column, string parameter, bool remove, bool prepend)

Parameters

column string

The quoted column.

parameter string

The bound items' parameter marker.

remove bool

Whether the items are removed instead of added.

prepend bool

Whether added items go at the front.

Returns

string

ConfigureParameter(DbParameter, object?)

Configures a just-created parameter for a bound value — the hook a dialect uses to type values its driver cannot infer (e.g. a dictionary into a jsonb parameter). The base dialect does nothing.

public override void ConfigureParameter(DbParameter parameter, object? value)

Parameters

parameter DbParameter

The parameter (its name and value are already set).

value object

The bound value.

Remarks

Npgsql cannot infer jsonb for a dictionary value; the parameter is typed explicitly.

CreateIndexSql(string, string, string, bool, IndexMethod, string?)

The DDL creating an index with a structure and an optional filtered predicate (already rendered as a SQL fragment with inlined literals). The base dialect builds default-structure indexes, filtered or not; a method it has no structure for is rejected with guidance.

public override string CreateIndexSql(string quotedName, string quotedTable, string columns, bool unique, IndexMethod method, string? filter)

Parameters

quotedName string

The quoted index name.

quotedTable string

The quoted table.

columns string

The rendered key column list.

unique bool

Whether the index enforces uniqueness.

method IndexMethod

The index structure.

filter string

The rendered partial-index predicate, or null.

Returns

string

DropColumnSql(string, string)

The DDL dropping a column from an existing table.

public override string DropColumnSql(string quotedTable, string quotedColumn)

Parameters

quotedTable string

The quoted table.

quotedColumn string

The quoted column.

Returns

string

InsertSql(string, string, string, string?)

Builds an INSERT, reading the generated key back when returningKey is supplied (RETURNING / OUTPUT INSERTED). The base dialect cannot read keys back — declare a client-generated key, or use a dialect that can.

public override string InsertSql(string quotedTable, string columns, string values, string? returningKey)

Parameters

quotedTable string

The quoted table.

columns string

The quoted column list.

values string

The bound value list.

returningKey string

The quoted generated-key column to read back, or null.

Returns

string

IsDocumentColumn(Type)

Whether a member of this CLR type maps as a document column (e.g. a scalar-keyed dictionary into PostgreSQL jsonb). The base dialect maps none — such members stay unmapped navigations.

public override bool IsDocumentColumn(Type type)

Parameters

type Type

The member's CLR type.

Returns

bool

Remarks

A scalar-keyed dictionary maps as a jsonb document column.

Quote(string)

Quotes an identifier (table or column name).

public override string Quote(string identifier)

Parameters

identifier string

Returns

string

SearchIndexSql(string, string, string)

The DDL materializing a model-declared search index (SearchIndex(...) / [SearchIndex]) on a text column, in execution order — empty when the dialect has no equivalent structure. The declaration never changes semantics (LIKE pushes down regardless); it only changes the plan, which is why a dialect without the structure ignores it instead of refusing.

public override IReadOnlyList<string> SearchIndexSql(string indexName, string quotedTable, string quotedColumn)

Parameters

indexName string

The unquoted index name (the dialect quotes it).

quotedTable string

The quoted table.

quotedColumn string

The quoted column.

Returns

IReadOnlyList<string>

Remarks

A GIN trigram index (pg_trgm): it serves LIKE/ILIKE with leading wildcards — Contains/EndsWith stop scanning. The extension creation is idempotent and ships with stock PostgreSQL; it does require the privilege to create extensions in the database.

SqlType(Type)

The DDL type for a CLR type (used by CREATE TABLE migrations).

public override string SqlType(Type type)

Parameters

type Type

Returns

string

Synonym(string)

The one spelling this dialect uses for a type named without its facets.

protected override string Synonym(string baseType)

Parameters

baseType string

The lower-cased type name.

Returns

string

TupleComparison(IReadOnlyList<string>, ComparisonOperator, IReadOnlyList<string>)

Renders a row-wise tuple comparison; the base dialect cannot express it.

public override string TupleComparison(IReadOnlyList<string> columns, ComparisonOperator op, IReadOnlyList<string> parameters)

Parameters

columns IReadOnlyList<string>

The quoted columns.

op ComparisonOperator

The comparison operator.

parameters IReadOnlyList<string>

The bound values' parameter markers.

Returns

string