主页 / dba / 问题 / 225866
Accepted
Gabriel
Asked: 2018-12-28 09:20:38 +0800 CST

有没有办法根据每天创建的表自动更新视图？

  • 772

假设我有一个每天创建的表：

20181227
20181226
20181225
...

有这样的观点：

CREATE OR REPLACE VIEW someview
AS 
SELECT * FROM 20181227
UNION
SELECT * FROM 20181226
UNION
SELECT * FROM 20181225
...

有没有一种方法可以用另一个代码替换它，该代码可以自动从所有这些表中获取数据而不必为每个表创建一个联合？现在有一个每天运行的 bash 脚本，每天用一个新的联合重新生成视图，但这并不优雅。

database-design postgresql

2 个回答

  1. Best Answer

    查看表分区。理想情况下，使用最新版本的 Postgres（当前为 Postgres 11），因为 Postgres 10 和 11 有了重大改进。

    Postgres 11 允许RANGE、LIST和HASH分区。每天一个大分区会建议基于列的LISTdate分区，例如：

    CREATE TABLE foo (
  foo_date date NOT NULL
, foo_id   bigint NOT NULL GENERATED ALWAYS AS IDENTITY
, data     text
, PRIMARY KEY (foo_date, foo_id)
)
PARTITION BY LIST (foo_date);

CREATE TABLE foo_20181226 PARTITION OF foo FOR VALUES IN ('20181226');
CREATE TABLE foo_20181227 PARTITION OF foo FOR VALUES IN ('20181227');
-- etc.

    （或者，您可能有一个timestamportimestamptz列并为此使用RANGE分区。）

    然后你可以直接查询主表自动包含所有分区：

    SELECT * FROM foo;

    或者（因为您主要关心的似乎是简短的语法）：

    TABLE foo;

    看：

    可以进行各种优化，包括约束、索引、列默认值等，具体取决于需求细节。

    请务必阅读手册的链接章节以了解各种优缺点。您的应该是完美的用例（除非有未公开的要求）。特别是，您可以轻松快速地添加和删除分区，而对表的其余部分的干扰最小。

    目前的实施仍然存在局限性。尤其是分区剪枝对性能的提升有很大帮助，但还有改进的空间（目前正在开发中）。也就是说，对于不需要涉及所有表（分区）的查询，它可能会影响视图的性能，因为视图每次都会考虑所有联合表。对于某些查询，一种可能更快的替代方法是（当前）在自定义UNION ALL查询中选择相关表（分区）。

    并且不要像您的示例中那样使用仅由数字组成的名称20181225。使用以字母开头的合法小写名称，或者您必须始终将标识符用双引号引起来。

    SQL 标识符和关键字必须以字母（a- z，以及带变音符号和非拉丁字母的字母）或下划线 ( _) 开头。标识符或关键字中的后续字符可以是字母、下划线、数字 ( 0- 9) 或美元符号 ( $)。请注意，根据 SQL 标准的规定，标识符中不允许使用美元符号，因此使用它们可能会降低应用程序的可移植性。SQL 标准不会定义包含数字或以下划线开头或结尾的关键字，因此这种形式的标识符是安全的，不会与标准的未来扩展发生冲突。

    有关的：

    • 2

  2. 从我的角度来看，您应该将数据添加到现有表中，并为“Date_Added”添加一个新字段，并将所有查询都基于该表。否则，几年后您的结构将变得不可读。

    • 1

相关问题