是否可以在每个记录标签上使用PG序列? [英] Is it possible to use a PG sequence on a per record label?
问题描述
PostgreSQL 9.2+是否提供了任何功能,可以生成一个命名空间到特定值的序列?例如:
Does PostgreSQL 9.2+ provide any functionality to make it possible to generate a sequence that is namespaced to a particular value? For example:
.. | user_id | seq_id | body | ...
----------------------------------
- | 4 | 1 | "abc...."
- | 4 | 2 | "def...."
- | 5 | 1 | "ghi...."
- | 5 | 2 | "xyz...."
- | 5 | 3 | "123...."
这将有助于为用户生成自定义网址: p>
This would be useful to generate custom urls for the user:
domain.me/username_4/posts/1
domain.me/username_4/posts/2
domain.me/username_5/posts/1
domain.me/username_5/posts/2
domain.me/username_5/posts/3
我没有在PG文档中找到任何东西(关于序列和序列函数)。是 INSERT
语句中的子查询还是自定义PG函数只有其他选项?
I did not find anything in the PG docs (regarding sequence and sequence functions) to do this. Are sub-queries in the INSERT
statement or with custom PG functions the only other options?
推荐答案
您可以在 INSERT
语句中使用子查询,如 @ Clodoaldo演示。但是,这违反了序列在并发事务中安全使用的性质。
You can use a subquery in the INSERT
statement like @Clodoaldo demonstrates. However, this defeats the nature of a sequence as being safe to use in concurrent transactions. This solution can and will result in race conditions and eventually duplicate key violations.
您应该重新思考您的方法。只需一个简单的序列为您的表,并与 user_id
,以获得您想要的排序顺序。
You should rather rethink your approach. Just one plain sequence for your table and combine it with user_id
to get the sort order you want.
总是使用 row_number()
生成带有所需数字的自定义网址:
You can always generate the custom urls with the desired numbers using row_number()
with a simple query like:
SELECT format('domain.me/username_%s/posts/%s'
,user_id
,row_number() OVER (PARTITION BY user_id ORDER BY seq_id)
)
FROM t;
- > SQLfiddle 。
-> SQLfiddle.
这篇关于是否可以在每个记录标签上使用PG序列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!