That is what I use now, together with the select as Hick mentioned.
An other version I played with is two indexes, one unique on (id,
ts_from) to find the last version if eol is not null and one unique on
(id, ts_eol) where eol = null to find the current active version of id.
Beware that in
On 21-6-2019 11:46, Simon Slavin wrote:
> You will note that SQLite is perfectly happy with
>
> CREATE UNIQUE INDEX idx_test_c
>ON test(id, ts_from, ts_eol)
> WHERE ts_eol = NULL
> ;
That is what I use now, together with the select as Hick mentioned.
An other version I played with is two
On 21 Jun 2019, at 10:29am, ingo wrote:
> Is this because max() is not deterministic,
> or because current_timestamp is not,
> or both?
The max(). For the reason you identified: it would have to recalculate the
index values for all rows in the table every time any of the rows changed. You
CREATE TABLE test(
id INTEGER NOT NULL,
ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
ts_eol TEXT DEFAULT NULL
);
CREATE UNIQUE INDEX idx_test
ON test(id, max(ts_from), ts_eol)
WHERE ts_eol = NULL
;
--Error: misuse of aggregate function max()
Is this because max() is not
4 matches
Mail list logo
