问题[postgresql](coding)

在搜索了这个问题之后，不确定是否真的可以这样做，感谢您的指点。

我的观点是这样创建的：

create or replace view OutputView as
select * from view1
natural full outer join view2;

使用：

update OutputView set colA = 'undefined' where colA is null;

给出：

ERROR:  cannot update view "OutputView"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

到目前为止，该触发器和规则还没有成功。问题在于输出视图中的某些列具有空值，因为这些列在原始视图中不存在，例如：

view1
-----
colA colB
1    a
2    b

view2
-----
colB colC
100  x
200  y

OutputView
----------
colA  colB  colC
1     a     null
2     b     null
null  100   x
null  200   y

我在创建具有一对一关系的新实体时遇到了问题。EF Core 无法正确分配外键。

作为示例，我们以两个实体为例：

public class User
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Blog
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
}

定义它们之间的关系，以便清楚地了解哪个依赖于哪个：

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<User>()
        .HasOne(u => u.Blog)
        .WithOne(b => b.User)
        .HasForeignKey<Blog>()
        .IsRequired();
}

然后当我尝试像这样创建它们时：

var user = new User 
               {
                   Blog = new Blog(),
               };

await DbContext.Set<User>().AddAsync(user);
await DbContext.SaveChangesAsync();

已Blog创建，其UserId属性也已正确设置。但是，的BlogId属性User为 0。根据文档，应该可以像这样创建关联实体。

这不适用于一对一关系吗？还是我还遗漏了什么？

顺便说一下，我用的是 Postgres，万一数据库提供商跟它有关呢。另外，EF Core 版本是 9。

我的 Postgres 17 数据库有一个大表，其中有 245 列，其中包含一个索引列ribakood：

CREATE TABLE firma2.toode
(
...
    ribakood character(20) COLLATE pg_catalog."default",
...
);

CREATE INDEX toode_ribakood_idx
    ON firma2.toode 
    (ribakood COLLATE pg_catalog."default" ASC NULLS LAST);

查询如下trim()：

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood=TRIM('TESTTOODE/H ')

不使用索引：

Gather  (cost=1000.00..575155.04 rows=4927 width=114) (actual time=101.341..2257.639 rows=1 loops=1)
  Workers Planned: 3
  Workers Launched: 0
  ->  Parallel Seq Scan on toode (cost=0.00..573662.34 rows=1589 width=114) (actual time=101.186..2257.436 rows=1 loops=1)
        Filter: ((ribakood)::text = 'TESTTOODE/H'::text)
        Rows Removed by Filter: 986481
Planning Time: 0.098 ms
Execution Time: 2257.653 ms

相同的查询，但不包含trim()：

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood='TESTTOODE/H'

使用索引：

Index Scan using toode_ribakood_idx on toode  (cost=0.42..12.45 rows=2 width=114) (actual time=0.475..0.477 rows=1 loops=1)
  Index Cond: (ribakood = 'TESTTOODE/H'::bpchar)
Planning Time: 0.147 ms
Execution Time: 0.490 ms

Postgres 将ribakood列强制转换为类型text，但找不到索引。如何强制第一个查询使用trim()索引？

使用：x86_64-windows 上的 PostgreSQL 17.0，由 msvc-19.41.34120 编译，64 位

id        timestamp              value
--------+---------------------+-----------
225     |2018-07-24 13:00:00  |0
226     |2018-07-24 18:33:32  |196
227     |2018-07-25 5:59:14   |290
173     |2018-07-05 8:16:14   |258
228     |2018-07-25 7:00:00   |469
175     |2018-07-07 6:00:00   |0
176     |2018-07-07 9:25:35   |236
177     |2018-07-07 17:19:13  |300
178     |2018-07-08 5:47:13   |323
179     |2018-07-08 6:49:14   |230
180     |2018-07-08 17:45:36  |270
181     |2018-07-09 5:41:13   |250
182     |2018-07-09 9:43:13   |213
183     |2018-07-09 18:37:44  |226
184     |2018-07-10 5:22:44   |380
185     |2018-07-10 7:37:14   |200
186     |2018-07-10 15:54:44  |260

SELECT max(value)  
  FROM table 
 WHERE timestamp BETWEEN '2018-07-22 00:00:00' 
                     AND '2025-04-22 23:59:59'

这是我对特定时期的查询，我需要它获取表中所有数据的每天的最高值或每天的最高值。

这是我对特定时期的查询，我需要它获取表中所有数据的每天的最高值或每天的最高值。

如果用户对函数中引用的对象没有权限，那么当他们尝试执行该函数时，具有该函数的 EXECUTE 权限的用户将收到错误。

我创建了一个函数 my_function。在该函数中，我从表 my_table 中执行 select/insert/update/delete 操作。然后，我将 EXECUTE 权限授予另一个用户“user1”。

但是，当用户 1 尝试执行该功能时，它会失败并显示消息“拒绝对表 my_table 的权限”。

我不想授予用户 user1 直接访问表 my_table 的权限。这正是创建函数 my_function 的全部目的，因为我想控制 user1 可以查看/修改哪些数据。

这是 Postgres 的限制（缺陷）吗？对我来说，这是一个非常严重的安全/隐私问题。有办法解决这个问题吗？还是我们必须忍受它？

我的 Postgres 版本是 15.x

我需要连续迁移两个非常大的表。我的第一个解决方案是逐列比较以找出哪些发生了更改，但这样做成本太高。

所以第二个想法是创建持久化计算列来存储集合列的散列值。这可以加快处理速度。但问题是，使用带时区的时间戳对列进行散列时出现了问题。我收到了这个错误：ERROR: generation expression is not immutable。

我尝试了各种方法来转换值，但都不起作用。

代码（注释值，无效）：

ALTER TABLE schema.table ADD COLUMN hashed_columns UUID GENERATED ALWAYS AS (
    md5(
        id::text ||
        type_id::text ||
        --EXTRACT(EPOCH FROM date)::text ||
        -- to_char(date::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS') ||
        -- date::text ||
        -- to_char(date, 'YYYYMMDD')::integer ||
        -- md5(date::text) ||
        to_char(date, 'YYYYMMDD') ||
        value1::text ||
        value2::text
    )::uuid
) STORED;

看起来，它检测到了不可变列，然后直接抛出错误，而没有先尝试强制类型转换。我尝试做的事情从根本上来说错了吗？还是说有可能？

当我从脚本中删除日期列时，它可以工作，所以问题出在时间戳内。

服务器正在运行 Postgre 16。

我正在学习使用递归 CTE 进行图遍历（learnSQL 课程）。题目问的是从一个起始城市访问每个城市。他们使用一个由附加城市名称构建的“路径”字符串，然后用它来where position(city.name IN travel.path) = 0判断该城市是否已经被访问过。这种方法可行，但我经常寻找替代方案，所以我可以使用一个已访问城市数组。

作为一名经验丰富的开发人员（例如 Java 或任何命令式语言），我会将其表示visited为 SET 以进行有效的遏制测试，但 postgresql 似乎只提供 ARRAY。

1. 是否有我可以使用的 SET 类型（简单的类型，而不是单独的表或 JSON 集）？
2. ARRAY 在查找方面是否高效？

当然，示例数据很小，这没什么问题，但我想知道最有效/最佳实践是什么。在我看来，使用字符串查找语义position不太好。

谢谢！

with recursive
travel(path, visited, visited_count) AS (
  select
    name::text,
    array[name::text],
    1
  
  from city
  where
    name = 'Bristol'
  
  union all
  
  select
    travel.path || '->' || city.name,
    visited || name::text,
    visited_count + 1
  
  from city, travel
  
  where
    city.name <> all(visited)
    --position(city.name IN travel.path) = 0
)
  
select
  path,
  visited

from travel

where
  visited_count = 6

问题和背景：

我正在尝试将 Apache Ignite (3.0.0) 配置为内存数据网格。想法是将其用作缓存并从外部 RDBMS（如 postgres）加载数据。我有一个特定的用例，其中我有一些数据文件，它们在 postgres 中具有 XML 类型和 TXT 类型。我希望能够在 apache ignite 中加载数据（通过将其设置为缓存），然后在我的 java 和 cpp 应用程序中使用它。

我面临的问题是，网上没有针对此特定版本的 apache ignite 进行设置的明确说明和有用资料。

我目前所做的：

我已经在我的机器（Linux子系统）上下载了Apache Ignite 3二进制文件。我成功地设置了它，并启动了一个单节点集群。我能够使用GridGain UI以及IgniteTables API创建一个包含示例数据的表格。

到目前为止，我已经阅读了官方文档：https: //ignite.apache.org/docs/ignite3/latest/index

我也浏览了他们的 github 存储库中的示例：https://github.com/apache/ignite-3/tree/main/examples

我在 2.x 版 Apache Igite 中找到了不少关于如何执行此操作的信息，该版本的设置是通过 xml/java 配置完成的（具体参考：https ://ignite.apache.org/docs/latest/persistence/external-storage#rdbms-integration ）。
我也尝试了这些方法，但 CacheConfiguration 包/类似乎在最新版本（3.0.0）中不可用。GridGain
的另一个更新是，他们已经迁移到 HOCON 风格的配置，因此通过 xml 设置缓存似乎不再适用。

• 基于 HOCON 的配置：人性化优化配置对象表示法 (HOCON) 现在是标准配置语言，它提供了一种比 XML 更易读、更灵活的替代方案。

我还查看了 Gridgain 大学和官方 Youtube 频道的课程/视频。

我正在使用 PostgreSQL 数据库，需要更改特定文本列的排序规则设置。但是，我不想更改整个数据库的排序规则。

数据库目前在整数列（特别是 ID 列）上建立了不区分大小写的索引，而我想要修改的文本列上没有索引。我假设仅更改文本列的排序规则应该不会造成任何问题，因为这些列上没有索引。

我的问题是：即使数据库的排序规则没有改变并且没有索引受到影响，更改文本列上的排序规则是否会导致现有数据或查询出现任何问题？

在这种情况下我应该注意哪些性能考虑或潜在问题？

当前数据库排序规则设置为Turkish_Turkey.1254，如以下查询所示：

SELECT datname, datcollate, datctype 
FROM pg_database 
WHERE datname = '';

要将数据库中所有文本类型列的排序规则更改为不区分大小写的排序规则，我运行以下 PL/pgSQL 块：

DO $$
DECLARE
    t record;
BEGIN
    FOR t IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_catalog = '' 
          AND data_type = 'text' 
          AND table_schema NOT LIKE 'pg_%'
        ORDER BY table_schema
    LOOP
        EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text COLLATE "tr-x-icu"', 
                       t.table_schema, t.table_name, t.column_name);
    END LOOP;
END
$$;

CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
  RETURNS void AS
$func$
DECLARE
   _dbname TEXT := concat(_name, '_db');
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _name) THEN
       RAISE NOTICE 'Role already exists. Skipping.';
    ELSE
     CREATE USER _name WITH encrypted password '_pass';
     GRANT ALL PRIVILEGES ON DATABASE _dbname TO _name;
    END IF;
END
$func$ LANGUAGE plpgsql;

当我跑步时：

psql --username "$POSTGRES_USER" -c "SELECT create_user_if_not_exists('Foo', 'BAR');"

我得到：

ERROR:  database "_dbname" does not exist

如果注释掉这GRANT ALL部分，我最终会得到一个名为 的用户_name。pg_catalog.pg_roles我怀疑_这是造成这种情况的原因，但其他语法也一样。添加_name和_pass并DECLARE不会改变任何事情。

为什么在这种情况下_name， 、_pass和_dbname会被解释为值而不是真正的变量？这是函数定义的问题，还是对GRANT和有一些限制，CREATE或者因为我用 调用这个函数的方式psql？

读完这篇文章和这篇文章后，我仍不明白解释错误之处的区别。

环境：psql (PostgreSQL) 17.4 (Debian 17.4-1.pgdg120+2)