[evangow]

Since we're here talking about Postgres and SQL standards. Here's my wishlist:

- Support for arrays of foreign keys. This one has been worked on a couple of times but hasn't made it into a release. https://commitfest.postgresql.org/17/1252/ - I think this would be an absolutely phenomenal way of handling sorting, instead of having to use linked lists and CTEs or some type of "order/position" column

- Support for deferring NOT NULL and CHECK constraints to the end of a transaction (just ran into this problem yesterday)

Edit: typos

[paulddraper]

> Support for arrays of foreign keys.

Standards-wise, the first step would be arrays in general :)

> Support for deferring NOT NULL and CHECK constraints to the end of a transaction

Agreed 100%

[zwily]

“Array of foreign keys” has been at the top of my list for so long. Would simplify sooooo much.

[_a_a_a_]

I followed the link about arrays of foreign keys but couldn't see what it was saying. Could you briefly summarise for me?

[meotimdihia]

Is there a database that supported arrays of foreign keys?

[paulddraper]

Of the major databases, only Oracle and PostgreSQL have array extensions and neither support FKs as array elements.

I'm curious how people think this feature should work? ON DELETE SET NULL, ON DELETE CASCADE, something else?

[lolive]

We use it in Palantir, that is a proprietary overlay on top of Spark. It supports this feature reasonably fine.

[petereisentraut]

> - Support for deferring NOT NULL and CHECK constraints to the end of a transaction (just ran into this problem yesterday)

I'm curious what the use case of this is?

Deferrable constraints are usually considered for foreign keys, since there you might have to juggle updates to multiple tables and might violate the constraint in the intermediate states. But that doesn't appear to apply in that way to CHECK constraints.

[evangow]

2 tables that reference each other and the foreign key columns both have a NOT NULL constraint.