Fuaran.DB.Dialect.PostgreSql 0.1.0

Fuaran.DB.Dialect.PostgreSql

The PostgreSQL dialect of the Fuaran.DB provider. It decides how a statement is spelled, which constructs it does not lower, and how a host binds a parameter and decodes a result cell. It opens no connection and references no driver. The host binds PostgreSql.parameterValue, and hands each result cell, as its driver read it, to PostgreSql.decodeCell. It needs PostgreSQL 11 or later, for starts_with.

Spelling

• Identifiers are double-quoted, and a double quote inside one is doubled: a"b is "a""b". A quoted identifier keeps its case. The provider never writes an unquoted one, so a table created unquoted (and therefore folded to lower case) must be modelled under its lower-case name.
• Parameters are positional markers, $1, $2, …. The marker for ordinal 0 is $1. A host adds the parameters in ordinal order, each with the engine type below.
• Strings are read under the C collation. Every scanned string column and every string parameter is written (… COLLATE "C"), so strings compare, group and order by code point, whatever the column or the database declares. A linguistic collation would order a before B, which the reference does not.
• Paging is ORDER BY key ASC|DESC NULLS LAST, … LIMIT $n OFFSET $m. The provider has already clamped both counts at zero.
• Booleans are PostgreSQL's own: a boolean value is a condition, and a condition is a value.

The type map

The exact decimal is an unconstrained numeric, which holds the reference's value exactly. A value outside the .NET decimal's range is refused by parameterValue by name. A timestamp keeps the fractional digits its unit states. timestamp holds at most six (1 microsecond), so a nanosecond timestamp is refused by name (typeRefusal): createTable answers Error naming the column, and a nanosecond parameter is a refused construct. A reported timestamp keeps the unit its precision states, the coarsest that holds its digits (a bare timestamp is the engine's default of six). A timestamp value travels at its column's unit: parameterValue binds a DateTime exact to the cell's fraction, and decodeCell writes the engine's value at the column's unit, refusing by name one finer than the unit rather than truncating it. The following read as no column type:

• bigint, which outgrows int32.
• timestamp with time zone, which carries an offset.
• character, which compares with its padding ignored.
• money, which formats by the session's locale.
• Binary and every other type.

A date binds as a DateOnly, and a timestamp as a DateTime of unspecified kind.

What it lowers

• Functions:
  • Abs.
  • Round, spelled sign(x) * floor(abs(x) + 0.5). That is the reference's own rounding, in the operand's arithmetic, where round would round a double half to even.
  • Floor and Ceil.
  • Trim, over the reference's pinned set: space, tab, carriage return and line feed.
  • Sqrt, which answers null for a negative argument as the reference does, where sqrt would raise.
  • Replace, which is literal and case-sensitive. An empty search leaves the text as it is.
• Operators: +, -, *, the comparisons, AND and OR. The substring tests read no wildcard: Contains is strpos(a, b) > 0, StartsWith is starts_with(a, b), and EndsWith compares right(a, length(b)) with b.
• Aggregates:
  • Count and CountDistinct. Both answer a bigint, which decodeCell narrows.
  • Sum. Over integer it answers a bigint, which decodeCell refuses past int32 with the reference's own refusal.
  • Mean, as avg over a double precision cast.
  • Min and Max. Over boolean they are bool_and and bool_or.
• Joins: every join kind the provider lowers.

Every refusal it declares

Each refusal places the fold boundary at the step that uses the construct, and that step runs locally in the reference evaluator. The conformance kit has a fixture that reaches each one.

Every difference it pins

The last column is what the recorded engine run measured (PostgreSQL 17.11, DECISIONS.md D11.5).

Certification

Fuaran.DB.Conformance runs its fixtures through this dialect and a host's executor, and reports each one as agreed, differing as pinned, or differing unexpectedly. When FUARAN_DB_POSTGRESQL holds a connection string, the repository's suite runs the kit against that server. It creates a UTF-8 database from template0 for the run and drops it afterwards, which needs PostgreSQL 13. It runs the falsifier, a dialect that reads strings bare, over a database under the ICU root locale, which needs PostgreSQL 15, and runs this dialect over that same database, where it must still agree. When the variable is not set, the suite reports the leg as Pending.

The recorded run (DECISIONS.md D11.5) was made on PostgreSQL 17.11 (x86-64 build, under emulation on ARM64 Windows), in a UTF-8 database under the libc collation English_United Kingdom.1252. Of 64 fixtures, 63 agreed, 1 differed as pinned (difference-code-point-order) and 0 differed unexpectedly, before any change to this dialect, which therefore needed none. Over the ICU root locale this dialect also gave 63, 1 and 0, and the falsifier went red there on difference-collation-order. That run covers what the in-memory run cannot: the spelling, the type map in both directions, result decoding, the COLLATE "C" read, the Round formula (filter-round), and the four constructs SQL Server refuses (reach-replace, reach-contains, reach-starts-with, reach-ends-with).