pg18 – select * from depesz;

Waiting for PostgreSQL 18 – Add SQL function array_reverse()

On 1st of November 2024, Michael Paquier committed patch:

Add SQL function array_reverse()
 
This function takes in input an array, and reverses the position of all
its elements.  This operation only affects the first dimension of the
array, like array_shuffle().
 
The implementation structure is inspired by array_shuffle(), with a
subroutine called array_reverse_n() that may come in handy in the
future, should more functions able to reverse portions of arrays be
introduced.
 
Bump catalog version.
 
Author: Aleksander Alekseev
Reviewed-by: Ashutosh Bapat, Tom Lane, Vladlen Popolitov
Discussion: https://postgr.es/m/CAJ7c6TMpeO_ke+QGOaAx9xdJuxa7r=49-anMh3G5476e3CX1CA@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add SQL function array_reverse()

Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints

On 17th of September 2024, Peter Eisentraut committed patch:

Add temporal FOREIGN KEY contraints
 
Add PERIOD clause to foreign key constraint definitions.  This is
supported for range and multirange types.  Temporal foreign keys check
for range containment instead of equality.
 
This feature matches the behavior of the SQL standard temporal foreign
keys, but it works on PostgreSQL's native ranges instead of SQL's
"periods", which don't exist in PostgreSQL (yet).
 
Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
are not supported yet.
 
(previously committed as 34768ee3616, reverted by 8aee330af55; this is
essentially unchanged from those)
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints

Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints

On 17th of September 2024, Peter Eisentraut committed patch:

Add temporal PRIMARY KEY and UNIQUE constraints
 
Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints.
These are backed by GiST indexes instead of B-tree indexes, since they
are essentially exclusion constraints with = for the scalar parts of
the key and && for the temporal part.
 
(previously committed as 46a0cd4cefb, reverted by 46a0cd4cefb; the new
part is this:)
 
Because 'empty' && 'empty' is false, the temporal PK/UQ constraint
allowed duplicates, which is confusing to users and breaks internal
expectations.  For instance, when GROUP BY checks functional
dependencies on the PK, it allows selecting other columns from the
table, but in the presence of duplicate keys you could get the value
from any of their rows.  So we need to forbid empties.
 
This all means that at the moment we can only support ranges and
multiranges for temporal PK/UQs, unlike the original patch (above).
Documentation and tests for this are added.  But this could
conceivably be extended by introducing some more general support for
the notion of "empty" for other types.
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints

=$
|

Tag: pg18

Waiting for PostgreSQL 18 – Add SQL function array_reverse()

Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints

Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints