PostgreSQL:如何用多种货币存钱？

Question

我有一个应用程序可以处理不同货币的产品和销售。因此，数据库中同一表中的每一行都可以存储不同货币的价格。如何正确地做到这一点？
最直接的方法是定义一个数字 price_amount列和 varchar price_currency oolumn，但我觉得两个技术上独立的列基本上是单个值( price )是错误的。就像物理测量是没有单位的毫无意义的数字一样，货币的数量如果没有其单位——货币，也是毫无意义的。
在我看来，钱应该是一个包含金额和货币的单一值 .
我开始搜索并惊讶地发现搜索结果中没有现成的解决方案或好的文章。有pg-currency做我想要的扩展，但它在大约 10 年前被放弃了。
我创建了以下 composite datatype作为起点:

CREATE TYPE true_money AS (
  currency varchar,
  amount numeric
);

然后开始为它编写支持性的东西:验证、算术、聚合……并意识到这个兔子洞真的很深。
可以在此处找到我对这种复合类型的所有当前(部分)结果以供引用: https://gist.github.com/Envek/780b917e72a86c123776ee763b8dd986?fbclid=IwAR2GxGUVPg5FtN3SSPhQv2uFA7oPNNjbZeTYWRix-ZijYaJFRec15chWLA8#file-true_money-sql
现在我可以做以下事情:

INSERT INTO "products" ("title", "price") VALUES ('Гравицапа', ('RUB',100500));
INSERT INTO "products" ("title", "price") VALUES ('Эцих с гвоздями', ('RUB',12100.42));
INSERT INTO "products" ("title", "price") VALUES ('Gravizapa', ('USD',19999.99));

-- You can access its parts if you need to extract them or do filtering or grouping
SELECT SUM(price) FROM test_monetaries WHERE (price).currency = 'RUB';
-- (RUB,112600.42)

-- And if you forget filtering/grouping then custom types can save you from nonsense results
SELECT SUM(price) FROM test_monetaries;
ERROR:  (USD,19999.99) can not be added to (RUB,112600.42) - currencies do not match

这是正确的方法吗？怎么做才对？
一点背景:在我们的应用程序中，用户(卖家)可以用他们想要的任何货币(例如，美元、欧元、日元、卢布等)管理他们的库存(产品)。该应用程序将转换货币并在本地站点(如英国或澳大利亚)上发布产品。买家还将以本地货币(英镑、澳元等)购买这些商品，这些商品最终将转换为卖家货币并支付给他们(费用除外)。所以在应用程序的很多地方，几乎任何支持的货币都可以出现。最后，卖家应该能够更改他们的货币，所有产品都应该批量转换为新货币(由于某些原因，交易中的单一更新不能使用)。所以我们不能说“在 products 中只保留数值”表并加入 sellers获取货币的表格”(我相信这本身就是反模式)。

Answer 1

是的，如果您想将它与 PostgreSQL 无缝集成，那么创建您自己的类型需要做很多工作。
如果一件商品可以在不同的国家/地区销售并且在各地都有不同的价格，您应该相应地对数据进行建模。有汇率是不够的，因为同样的东西在日本可能比在中国贵。
如果您只对当前价格感兴趣，则可能如下所示:

CREATE TABLE currency (
   currency_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   denomination text CHECK (length(denomination) = 3) NOT NULL
);

CREATE TABLE exchange (
   from_curr_id bigint REFERENCES currency NOT NULL,
   to_curr_id   bigint REFERENCES currency NOT NULL,
   rate         numeric(10,5) NOT NULL
);

CREATE TABLE country (
   country_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name        text UNIQUE NOT NULL,
   currency_id bigint REFERENCES currency NOT NULL
);

CREATE TABLE product (
   product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   title      text NOT NULL,
);

CRATE TABLE price (
   country_id bigint REFERENCES country NOT NULL,
   product_id bigint REFERENCES product NOT NULL,
   amount     numeric(10,2) NOT NULL,
   PRIMARY KEY (product_id, country_id)
);

CREATE INDEX ON price (country_id);  -- for the foreign key

这样，每个产品在每个国家都可以有一定的价格，并且价格通过国家与货币相关联。
当然，现实世界可能更加复杂:

您可以在每个国家/地区拥有不止一种货币

您可能希望保留历史价格信息

最重要的是，您始终可以遵循一串外键，明确引导您获得所需的金额和货币。
用于货币之间的转换