是否可以在每个记录标签上使用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屋！