如何在PostgreSQL中事务性地插入max(order_field)+ 1的行 [英] How insert rows with max(order_field) + 1 transactionally in PostgreSQL
问题描述
我需要在PostgreSQL表中插入一行,该行的列的最大值包含该表的子集上同一列的+ 1。该列用于对子集中的行进行排序。
我正在尝试在插入后触发器中更新列的值,但我正在此列的不同行中获得重复的值。
在短时间内有大量插入的并发环境中,避免子集中排序列重复值的最佳方法是什么? p>
预先感谢
编辑:
该子集由同一表的另一列定义:列对所有相关行具有相同的值。
如果该列仅用于排序,则使用序列: / p>
创建表t(
column1整数,
ordering_column序列
);
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
I need to insert in a PostgreSQL table a row with a column containing the max value + 1 for this same column on a subset of the rows of the table. That column is used to ordering the rows in that subset.
I´m trying to update the column value in an after insert trigger but I´m obtaining duplicate values for this column in different rows.
What´s the best way to do that avoiding duplicate values for the ordering column in the subset in a concurrent environment with a lot of inserts in a short time?
Thanks in advance
EDIT: The subset is defined by another column of the same table: this column has the same value for all the related rows.
If that column is used only for ordering then use a sequence:
create table t (
column1 integer,
ordering_column serial
);
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
这篇关于如何在PostgreSQL中事务性地插入max(order_field)+ 1的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!