I use Postgres very rarely (MS SQL shop in DayJob of the last two decades) but keep an eye on it just-in-case. As such I'm no expert, but a couple of these seem generic enough that they and any counters apply elsewhere:
* Immutable-after-insert columns: I think this is a rare enough need that should be kept in triggers, or (as postgres supports loadable modules) an optional part.
* “Updated at”: supported for this was added to MS SQL Server as part of system versioned (temporal) tables – as postgres has modules for that, you might find a solution there. In any case it could be done with a trigger, and of course be careful to note that a no-op change (UPDATE tbl SET a=a) would still set the timestamp for “affected” rows.
* Insert-only FK enforcement: earlier me would have immediately shot this down, as deleting things was anathema to me. These days being able to purge things like PII is a significant concern, but I'd still do that via soft delete if referential integrity was important which it should be in an audit example. If soft delete isn't an option, perhaps use a shadow table holding the past IDs that the audit can refer to, making the relationship a little more obvious from the PoV of looking at the schema without supporting documentation.
* FK-to-range-not-absolute-value: I see the potential usefulness of this, but could imagine it being a maze of edge cases to implement!
* Auto-index on FK: A big no from me. Occasions where this would be very wasteful are numerous enough that it is safer the way things are (that is why most major DBs work this way). A slight twist on the idea might be a good compromise: add a WITH INDEX or similar syntax to FK creation as a shorthand (also WITH INDEX ALWAYS, and have just WITH INDEX not create an index if there is already something, even inexact like a compound index with the right column(s) foremost, already present).
* “view columns not null”: I assume this is for updating base tables of a view by using INSERT/UPDATE on the view. This seems odd: I would have thought that the table constraint would fire anyway? Or is he looking for the NULLability to show up when inspecting the schema (directly, or when projected through something like an ORM)?
* Optional GROUP BY when unambiguous: I see this being requested a lot, partly because mysql does something similar (in fact it even allows projecting columns without aggregate in the presence of an explicit grouping, though these days that option is off by default). I think it would lead to more errors than it would “fix” by letting unintended groupings fix sytax errors in broken ways that are not noticed until later.
* leading and trailing commas for column lists: that one will cause arguments (ref: javascript)! Leading commas would be useful for the way I like to align parts in SQL statements, though I imagine there are places where they might cause ambiguity.
* multiple WHERE clauses treated as AND conditions: I've wanted this too. As a work-around I often start with WHERE 1=1 (which any good query optimiser will throw away instantly) and make all the other conditions ANDs. Similarly, with multiple ORs you can start with WHERE 0=1. Though as always, be very careful with precedence when mixing AND & OR.
* Index hints: be careful what you wish for. They are a common source of performance issues in MS SQL Server environments as data patterns change over time and what was an optimal choice in dev becomes something grossly inefficient later.
* Nested transactions: a thousand times yes in SQL Server too. The need is rare, but when it appears the absence of support is massively inconvenient.
1 comment
[ 95.2 ms ] story [ 705 ms ] thread* Immutable-after-insert columns: I think this is a rare enough need that should be kept in triggers, or (as postgres supports loadable modules) an optional part.
* “Updated at”: supported for this was added to MS SQL Server as part of system versioned (temporal) tables – as postgres has modules for that, you might find a solution there. In any case it could be done with a trigger, and of course be careful to note that a no-op change (UPDATE tbl SET a=a) would still set the timestamp for “affected” rows.
* Insert-only FK enforcement: earlier me would have immediately shot this down, as deleting things was anathema to me. These days being able to purge things like PII is a significant concern, but I'd still do that via soft delete if referential integrity was important which it should be in an audit example. If soft delete isn't an option, perhaps use a shadow table holding the past IDs that the audit can refer to, making the relationship a little more obvious from the PoV of looking at the schema without supporting documentation.
* FK-to-range-not-absolute-value: I see the potential usefulness of this, but could imagine it being a maze of edge cases to implement!
* Auto-index on FK: A big no from me. Occasions where this would be very wasteful are numerous enough that it is safer the way things are (that is why most major DBs work this way). A slight twist on the idea might be a good compromise: add a WITH INDEX or similar syntax to FK creation as a shorthand (also WITH INDEX ALWAYS, and have just WITH INDEX not create an index if there is already something, even inexact like a compound index with the right column(s) foremost, already present).
* “view columns not null”: I assume this is for updating base tables of a view by using INSERT/UPDATE on the view. This seems odd: I would have thought that the table constraint would fire anyway? Or is he looking for the NULLability to show up when inspecting the schema (directly, or when projected through something like an ORM)?
* Optional GROUP BY when unambiguous: I see this being requested a lot, partly because mysql does something similar (in fact it even allows projecting columns without aggregate in the presence of an explicit grouping, though these days that option is off by default). I think it would lead to more errors than it would “fix” by letting unintended groupings fix sytax errors in broken ways that are not noticed until later.
* leading and trailing commas for column lists: that one will cause arguments (ref: javascript)! Leading commas would be useful for the way I like to align parts in SQL statements, though I imagine there are places where they might cause ambiguity.
* multiple WHERE clauses treated as AND conditions: I've wanted this too. As a work-around I often start with WHERE 1=1 (which any good query optimiser will throw away instantly) and make all the other conditions ANDs. Similarly, with multiple ORs you can start with WHERE 0=1. Though as always, be very careful with precedence when mixing AND & OR.
* Index hints: be careful what you wish for. They are a common source of performance issues in MS SQL Server environments as data patterns change over time and what was an optimal choice in dev becomes something grossly inefficient later.
* Nested transactions: a thousand times yes in SQL Server too. The need is rare, but when it appears the absence of support is massively inconvenient.