在postgres中进行选择（或插入）查询期间，如何将UUID转换为UUID5？ [英] How can I transform a UUID to a UUID5 during select (or insert) queries in postgres?

问题描述

是否可以将现有的postgres数据集中的UUID转换为UUID5？

Is there a way to convert a UUID to a UUID5 in an existing postgres data set?

我有一个主/从设置，可以通过pgologic复制特定的表&列。我需要将此复制数据的访问权限提供给第三方，但我想尽可能地使其匿名。

I have a master/slave setup with pglogical replicating specific tables & columns. I need to give access to this replicated data to a third party, but I want to anonymise it as much as possible.

表中的每一行都使用UUID（生成在代码中）作为主键，但是为了确保匿名性，我需要确保主&复制的表经过单向转换，使回溯更加困难。一些复制的表也互相引用，这意味着我不能只排除那些列-值需要在表之间匹配。
希望到目前为止，这是有道理的...

Every row in a table uses a UUID (generated in code) as primary key, but in order to ensure anonymity I need to ensure that the primary keys of the master & replicated tables are one-way transformed to make back-tracing harder. Some of the replicated tables refer to each other as well, meaning I can't just exclude those columns - values need to match between tables. Hopefully this makes sense so far...

有没有一种使用 uuid-ossp 的方法和 uuid_generate_v5（）本质上将现有的UUID用作v5 UUID的命名空间，将视图中的UUID返回给第三方（甚至用于转换特定列）复制期间）？

Is there a way of using uuid-ossp and uuid_generate_v5() to essentially use the existing UUIDs as the namespace for a v5 UUID, returning those in the view to the third party (or even to use to transform the specific columns during replication)?

我有点困惑。如果我可以完全按照Postgres进行排序，那么在允许第三方访问之前，我不必费心使用其他工具来转换数据，我可以给他们提供实时数据，而不是批量处理。但是，如果我需要涉及一些其他代码，那么我想那还不是世界末日...

I'm a little stumped. If I can get this sorted entirely in postgres I won't have to muck about with additional tools to transform the data before allowing the third party access, I can just give them real-time data instead of batched. But if I need to involve some additional code then I guess that's not the end of the world...

预先感谢！

推荐答案

仅向第三方（在单独的架构中）提供只读视图，并将对它们的数据访问限制为这些视图（或架构）。

Simply provide read-only views to a third-party (in a separate schema) and limit data access to them to those views (or schema).

然后可以使用查询定义视图，例如：

A view can then be defined with a query such as:

SELECT uuid_generate_v5(t.uuid, 'custom-third-party'), t.a, t.b, ...
FROM ...

这样，您还可以向不同的第三方提供不同的UUID，因为原始UUID在数据条目中应该是唯一的。

This way, you can also provide different UUID's to different third-parties as the original UUID's should be unique across data entries.

重写数据策略不会扩展，只会在不需要的地方增加开销。

Rewriting data strategies are not going to scale and would just add overhead where it is not needed.

这篇关于在postgres中进行选择（或插入）查询期间，如何将UUID转换为UUID5？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！