aculeft.blogg.se

LISTAGG() does not have the semantics of an ordered-set aggregate, so implementing it other than as a special case would mean allowing ordinary aggs to be called using WITHIN GROUP as an alternative to their normal syntax. Currently we require that ordered-set aggs be called using WITHIN GROUP, while ordinary aggs must not use WITHIN GROUP but may have an embedded ORDER BY clause in their arguments (as per the standard ARRAY_AGG()). Implementing this in the most straightforward way would require erasing many of the existing distinctions between ordered-set aggregate syntax and conventional aggregate syntax. LISTAGG() is the standard SQL equivalent of our string_agg(), but it uses the ordered-set aggregate syntax. These are cases where PG's behavior differs from the spec, or where an otherwise desirable feature is missing, but there is some technical issue that would make it harder to implement. These are cases where PG's behavior differs from the spec, but there appears to be no major obstacle to changing it to conform. Accordingly, if X is a row value containing null columns, PostgreSQL will return X, while the spec would require returning Y.

PostgreSQL applies only the "is not the null value" test to X. A consequence of this is that the rather odd rules for null tests of row types are applied. The spec defines COALESCE(X,Y) as a syntactic transformation to CASE WHEN X IS NOT NULL THEN X ELSE Y END (it leaves open the question of whether X is really evaluated twice by disallowing non-deterministic expressions or expressions with side effects in this context). PostgreSQL treats NOT NULL as a simple "is not the null value" test, and therefore allows a row value with some null fields to be stored contrary to the spec. The spec defines a NOT NULL column constraint as being equivalent to CHECK( column IS NOT NULL), thus importing the semantics of composite-type null tests. Issues not yet classified NOT NULL constraints on composite-type columns 8 Major features simply not implemented yet.7.3 Fixed in PG14: Unreserved keywords as select-list aliases without AS.7.2 Fixed in PG14: Obsolete syntax for substring().7.1 Fixed in PG14: Wrong return type for extract().7 Issues fixed in released PostgreSQL versions.6 Issues where PostgreSQL actually conforms to the spec.5.17 Lexing of string literals and comments.5.16 Early enforcement of non-deferrable UNIQUE.5.11 Trigger firing relative to referential constraint actions.5.7 Default transaction isolation level.5 Issues where we explicitly choose to disregard the spec (WONTFIX).4 Issues with some block at design level.3.4 DISTINCT aggregates as window functions.