如何从不同的架构将公共数据插入到临时表中？

Question

我不确定如何解决此问题：

我们从各种在线供应商（Amazon，Newegg等）导入订单信息。每个供应商对于他们的订单都有自己的特定术语和结构，我们已经将它们镜像到数据库中。我们的数据没有问题地导入数据库，但是，我面临的问题是编写一种方法，该方法将从数据库中提取必填字段，而不管架构如何。

例如，假设我们具有以下结构：

Newegg结构：

"OrderNumber" integer NOT NULL, -- The Order Number
"InvoiceNumber" integer, -- The invoice number
"OrderDate" timestamp without time zone, -- Create date.

亚马逊结构：

"amazonOrderId" character varying(25) NOT NULL, -- Amazon's unique, displayable identifier for an order.
"merchant-order-id" integer DEFAULT 0, -- A unique identifier optionally supplied for the order by the Merchant.
"purchase-date" timestamp with time zone, -- The date the order was placed.

如何选择这些项目并将它们放入临时表以供查询？

临时表如下所示：

"OrderNumber" character varying(25) NOT NULL,
"TransactionId" integer,
"PurchaseDate" timestamp with time zone

我了解到，有些数据库代表一个带有整数的订单号，而另一些则代表一个变化的字符。以解决我计划将数据类型转换为String值的问题。

有没有人建议我阅读有关内容，以帮助我理解这一点？

我不需要确切的答案，只需向正确的方向轻推即可。

数据将由Java使用，因此，如果有任何特定的Java类有帮助，请随时提出建议。

Answer 1

首先，您可以创建一个VIEW以提供此功能：

CREATE VIEW orders AS
SELECT '1'::int            AS source -- or any other tag to identify source
      ,"OrderNumber"::text AS order_nr
      ,"InvoiceNumber"     AS tansaction_id -- no cast .. is int already
      ,"OrderDate" AT TIME ZONE 'UTC' AS purchase_date -- !! see explanation
FROM   tbl_newegg

UNION  ALL  -- not UNION!
SELECT 2
       "amazonOrderId"
      ,"merchant-order-id"
      ,"purchase-date"
FROM   tbl_amazon;

您可以像查询其他任何表一样查询此视图：

SELECT * FROM orders WHERE order_nr = 123 AND source = 2;

• source如果order_nr不是唯一的，则是必需的。您还如何保证不同来源的唯一订单号？

• timestamp without time zone在全球范围内，A 是模棱两可的。它仅与时区有关。如果您将timestamp和混合使用timestamptz，则需要将其timestamp与AT TIME ZONE结构放置在特定的时区以使其正常工作。有关更多说明，请阅读此相关答案。

我使用UTC作为时区，您可能需要提供其他时间。一个简单的转换"OrderDate"::timestamptz将假定您当前的时区。AT TIME ZONE应用于中的timestamp结果timestamptz。这就是为什么我没有添加其他演员表的原因。

• 虽然你 可以 ，我建议不要在PostgreSQL中使用驼峰标识符 以往 。避免了多种可能的混乱。请注意我提供的小写字母标识符（现在没有多余的双引号）。

• 请勿将varchar(25)用作类型order_nr。只要使用text无任意长度调节剂，如果它必须是一个字符串。如果所有订单号仅由数字组成，integer或者bigint会更快。

性能

快速实现此目标的一种方法是实现视图。即，将结果写入（临时）表中：

CREATE TEMP TABLE tmp_orders AS
SELECT * FROM orders;

ANALYZE tmp_orders; -- temp tables are not auto-analyzed!

ALTER TABLE tmp_orders
ADD constraint orders_pk PRIMARY KEY (order_nr, source);

您 需要 一个索引。在我的示例中，主键约束自动提供索引。

如果表很大，则 在 创建临时表 之前， 请确保您有足够的 临时缓冲区 在RAM中进行处理。否则，它实际上会使您减速。 __

SET temp_buffers = 1000MB;

必须是会话中对临时html" target="_blank">对象的第一次调用。请勿仅针对您的会话将其设置为全局较高。无论如何，临时表会在会话结束时自动删除。

要估算您需要多少RAM，请一次创建表格并进行以下测量：

SELECT pg_size_pretty(pg_total_relation_size('tmp_orders'));

在dba.SE上的相关问题下，有关对象大小的更多信息。

仅当您必须在一个会话中处理多个查询时，才支付所有开销。对于其他用例，还有其他解决方案。如果您在查询时知道源表，则将查询直接定向到源表会更快。如果您不这样做，我会再次质疑您的独特性order_nr。如果确实可以保证唯一，则可以删除source我介绍的列。

对于仅一个或几个查询，使用视图而不是物化视图可能会更快。

我还要考虑一个 plpgsql函数 ，该 函数
一个在另一个表之后查询直到找到记录。考虑到开销，对于一些查询可能会便宜一些。当然，每个表都需要索引。

另外，如果您坚持text或坚持自己的意愿varchar，请order_nr考虑COLLATE "C"一下。