PostgreSQL列值必须按顺序排列 [英] PostgreSQL column values must be in a sequence
问题描述
我将如何在PostgreSQL中定义一列,以使每个值必须按顺序排列,而不是使用类型 serial
时获得的顺序,而应按值2排列除非列中已经存在值1,否则不能插入?
How would I define a column in PostgreSQL such that each value must be in a sequence, not the sequence you get when using type serial
but one such that a value 2 cannot be inserted unless there exists a value 1 already in the column?
推荐答案
从理论上讲,您可以使用像这样工作的约束。 (但是实际上它不起作用。)
Theoretically, you could use a constraint that worked like this. (But it won't work in practice.)
- 对行进行计数。
- 求值
max(column)-min(column)+ 1
。 - 比较结果。
在创建CHECK约束之前,您可能必须插入一行。如果您不这样做,则max(column)将返回NULL。
You'd probably have to insert one row before creating the CHECK constraint. If you didn't, max(column) would return NULL. With one row,
- 仅记录一行(1)。
- 评估
max(column)-min(column)+1
。 (1-1 + 1 = 1) - 比较结果。 (1 = 1)
- Count the rows (1).
- Evaluate
max(column) - min(column) + 1
. (1 - 1 + 1 = 1) - Compare the results. (1 = 1)
有10行。 。
- 计算行数(10)。
- 求值
max(column )-min(column)+ 1
。 (10-1 + 1 = 10) - 比较结果。 (10 = 10)
- Count the rows (10).
- Evaluate
max(column) - min(column) + 1
. (10 - 1 + 1 = 10) - Compare the results. (10 = 10)
序列从1开始并不重要;如果存在的话,这种检查方式将始终显示出差距。如果需要确保无间隙序列从1开始,则可以将其添加到CHECK约束中。
It doesn't matter whether the sequence starts at 1; this way of checking will always show a gap if one exists. If you needed to guarantee that the gapless sequence started at 1, you could add that to the CHECK constraint.
据我所知,没有任何方法可以使用任何当前的dbms声明式执行此操作。为此,您需要支持 CREATE ASSERTION
。 (但我可能是错的。)在PostgreSQL中,我认为您唯一的选择就是在多个AFTER触发器中包含过程代码。
As far as I know, there isn't any way to do this declaratively with any current dbms. To do it, you'd need support for CREATE ASSERTION
. (But I could be wrong.) In PostgreSQL, I think your only shot at this involves procedural code in multiple AFTER triggers.
我只有一张表需要畅通无阻。这是日历表。我们每天晚上运行一次查询,执行这些计算,它让我知道我是否有差距。
I only have one table that needs to be gapless. It's a calendar table. We run a query once a night that does these calculations, and it lets me know whether I have a gap.
这篇关于PostgreSQL列值必须按顺序排列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!