主页 / user-169748

Brylie Christopher Oxley's questions

Martin Hope
Brylie Christopher Oxley
Asked: 2022-04-18 08:03:43 +0800 CST
  • 3

我有一个具有以下结构的数据库：

日期 角色 类型 期间
2022-04-16 护士 准备食材 45
2022-04-17 护士 打扫 30
2022-04-17 志愿者 打扫 20
2022-04-17 护士 准备食材 60

注意：我事先不知道“类型”列中的值，因为它们是由用户定义的。此外，可以有多个具有重叠日期、角色和类型的行。

我正在使用一个图表库，希望将数据分组如下：

角色 准备食材 打扫
护士 105 30
志愿者 无效的 20

到目前为止，我可以使用以下查询对数据进行分组

select 
    role,
    type, 
    sum(duration) as total_minutes
from work
group by role, type;
角色 类型 总分钟数
护士 打扫 45
护士 准备食材 20
志愿者 打扫 15
志愿者 准备食材 43

如何“透视”/“转置”数据，以便每一行代表一个角色，其中一列包含每种工作类型的分钟总和？

实际上，我想转置类似于 Pandas DataFrame.pivot_table函数的数据，但只使用 SQL。

postgresql group-by
Martin Hope
Brylie Christopher Oxley
Asked: 2020-12-19 01:02:23 +0800 CST
  • 4

我们使用外部数据包装器在单个 PostgreSQL RDS 上跨数据库进行查询。外部数据包装服务器需要为每个将查询远程服务器的用户映射一个用户。但是，为每个用户添加用户映射可能被证明容易出错。

我们所有需要查询外部数据服务器的用户都有一个共享角色，例如role_name，在我们的 PostgreSQL 服务器上。

我们如何在用户之间共享外部数据包装服务器用户映射？

postgresql postgresql-fdw
Martin Hope
Brylie Christopher Oxley
Asked: 2019-01-23 09:18:41 +0800 CST
  • 3

我正在尝试编写一个 Postgres 触发器以在插入或更新新行之前取消嵌套数组字段。例如

SELECT
unnest(something)
FROM NEW

但是，这似乎会导致错误：

关系“新”不存在

如何在触发器函数中使用 NEW 行，以允许我取消嵌套数组字段以进行进一步处理？

创建表语句

以下是表结构的示例：

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

触发功能

这里是TRIGGER函数的本质：

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS 
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into 
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select 
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$ 
LANGUAGE plpgsql;

触发语句

触发器语句可以在 INSERT 和 UPDATE 之前或之后运行，只要数据在“迁移”表中镜像即可。

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey 说明 我们正在尝试将数据模型的这一部分转换为使用 ForeignKey 关系而不是 JSON 字段。触发器旨在作为一个临时步骤，以确保normalized_table有数据向前发展，同时我们从旧记录中回填/迁移数据。

postgresql trigger