PostgreSQL 列值必须按顺序排列
我如何在 PostgreSQL 中定义一列，使得每个值都必须位于一个序列中，而不是使用类型 serial 时获得的序列，而是一个除非存在值 1 否则无法插入值 2 的序列已经在专栏中了吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我在此处编写了使用 PL/PgSQL 实现无缝序列的详细示例。
一般的想法是，您需要一个表来存储序列值，并使用
SELECT ... FOR UPDATE后跟UPDATE- 或简写UPDATE 。 .. RETURNING- 在锁定行的同时从中获取值，直到事务提交或回滚。I wrote a detailed example of a gapless sequence implementation using PL/PgSQL here.
The general idea is that you want a table to store the sequence values, and you use
SELECT ... FOR UPDATEfollowed byUPDATE- or the shorthandUPDATE ... RETURNING- to get values from it while locking the row until your transaction commits or rolls back.理论上，您可以使用像这样工作的约束。 （但实际上行不通。）
max(column) - min(column) + 1。在创建 CHECK 约束之前，您可能必须插入一行。如果不这样做，max(column) 将返回 NULL。对于一行，
max(column) - min(column) + 1。 (1 - 1 + 1 = 1)有 10 行。 。
max(column) - min(column) + 1。 (10 - 1 + 1 = 10)序列是否从 1 开始并不重要；如果存在间隙，这种检查方式总是会显示间隙。如果您需要保证无间隙序列从 1 开始，您可以将其添加到 CHECK 约束中。
据我所知，没有任何方法可以使用任何当前的 dbms 来声明性地执行此操作。为此，您需要支持
CREATE ASSERTION。 （但我可能是错的。）在 PostgreSQL 中，我认为您唯一的解决方案涉及多个 AFTER 触发器中的过程代码。我只有一张桌子需要无间隙。这是一个日历表。我们每晚运行一次查询来进行这些计算，它让我知道是否有差距。
Theoretically, you could use a constraint that worked like this. (But it won't work in practice.)
max(column) - min(column) + 1.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,
max(column) - min(column) + 1. (1 - 1 + 1 = 1)With 10 rows . .
max(column) - min(column) + 1. (10 - 1 + 1 = 10)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.
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.
您编写一个
插入触发器或一个检查约束。但是，这仍然允许删除“1”，而“2”保留在表中，您可能也必须解决这个问题。You write an
on insert tiggeror acheck constraint. However, this will still allow to delete "1" afterwards and "2" stays in the table, you'll probably have to address this too.