从Schem中的每个表中获取所有单个列

Question

在我们的系统中，我们有1000多个表，每个表都有一个包含DateTime对象的'date'列。我想得到一个包含所有表中存在的每个日期的列表。我相信应该有一个简单的方法来做到这一点，但我对postgresql或sqlalchemy的知识非常有限。你知道吗

在postgresql中，我可以对两个表执行完全联接，但似乎没有一种方法可以对模式中的每个表（对于单个公共字段）执行联接。你知道吗

然后我尝试用python和sqlalchemy编程解决这个问题。对于每个表，我确实为'date'列创建了一个select distinct，然后将selectes列表设置为CompoundSelect对象的selects属性，然后执行。正如人们从一个丑陋的蛮力查询中所预期的，它现在已经运行了一个小时左右，我不确定它是否在某个地方悄无声息地崩溃，永远不会回来。你知道吗

有没有更干净更好的方法？你知道吗

Answer 1

您肯定希望在服务器上执行此操作，而不是在应用程序级别执行此操作，因为应用程序和服务器之间存在许多往返，而且中间结果中可能存在重复数据。你知道吗

因为需要处理1000多个表，所以应该使用系统目录并动态查询这些表。您需要一个函数来有效地执行此操作：

CREATE FUNCTION get_all_dates() RETURNS SETOF date AS $$
DECLARE
  tbl    name;
BEGIN
  FOR tbl IN SELECT 'public.' || tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    RETURN QUERY EXECUTE 'SELECT DISTINCT date::date FROM ' || tbl;
  END LOOP
END; $$ LANGUAGE plpgsql;

这将处理public模式中的所有表；根据需要进行更改。如果表在多个模式中，则需要在存储表的位置插入附加逻辑，或者可以将模式名作为函数的参数，多次调用函数并UNION结果。你知道吗

注意，您可能会从多个表中获得重复的date。您可以在调用函数的语句中删除这些重复项：

SELECT DISTINCT * FROM get_all_dates() ORDER BY 1;

该函数在内存中创建一个结果集，但如果1000+个表中的行中的不同日期数非常大，则结果将写入磁盘。如果希望发生这种情况，那么最好在函数的开头创建一个临时表，并将日期插入临时表中。你知道吗

Answer 2

最后返回到以前使用SqlAlchemy运行查询的解决方案。这使我能够并行化事情，运行一个小更快，因为它确实是一个非常大的查询。你知道吗

我知道数据集有助于这个查询的一些事情——我只希望每个表有不同的日期，并且这些日期是我的数据集中的主键。我最终使用了来自this wiki page的方法。查询中发送的代码如下所示：

WITH RECURSIVE t AS (
(SELECT date FROM schema.tablename ORDER BY date LIMIT 1) 
UNION ALL SELECT (SELECT knowledge_date FROM schema.table WHERE date > t.date ORDER BY date LIMIT 1)
FROM t WHERE t.date IS NOT NULL)
SELECT date FROM t WHERE date IS NOT NULL;

我把查询的结果拉到一个列表中，如果我的所有日期都不在列表中，那么就把它们保存起来，以备以后使用。有可能只需要在pgsql控制台中运行它，但是对我来说，本地保存比在db中查询temp表更容易。你知道吗