主页 / user-4363

AndreKR's questions

Martin Hope
AndreKR
Asked: 2022-09-12 16:43:40 +0800 CST
  • 5

对于 JOIN 中的每个右侧行，我想创建一个序列号，从每个左侧行的 1（或 0）开始。

例子：

create table persons (person_id int, person_name text);
create table places (place_id int, person_id int, place_name text);
insert into persons values (10, 'Aulus Agerius'), (20, 'Numerius Negidius');
insert into places values (10, 10, 'Anytown'), (20, 10, 'Timbuktu'), (30, 20, 'Podunk');

select person_name, place_name
from persons join places using (person_id)
order by person_id, place_id;

小提琴

期望的结果：

人名 地名 place_seq
奥卢斯·艾格瑞斯 任何城镇 1
奥卢斯·艾格瑞斯 廷巴克图 2
努梅里乌斯·内吉迪乌斯 波敦克 1

因此，例如 2 inplace_seq表示“这是为这个人找到的第二个地方”。

如何创建place_seq列？

postgresql
Martin Hope
AndreKR
Asked: 2022-04-26 18:12:06 +0800 CST
  • 3

这里有一些数据可以玩：

CREATE TABLE a (
    a_id   int  NOT NULL,
    a_prop text NOT NULL
);

CREATE TABLE b (
    b_id   int  NOT NULL,
    b_prop text NOT NULL
);

INSERT INTO a VALUES (1, 'blah'), (2, 'blah'), (4, 'not this one');
INSERT INTO b VALUES (1, 'blah'), (3, 'blah'), (5, 'not this one');

现在我想写一个返回的查询：

结果

一种可能性是：

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);

这要求我为OR ... IS NULL我有条件的每个领域都写。如果某些条件是日期范围等，这将变得更加冗长。

如果这是左连接：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_prop = 'blah'
AND (b_prop = 'blah' OR b_prop IS NULL);

我可以将条件移至ON子句以避免这种情况：

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id AND b_prop = 'blah'
WHERE a_prop = 'blah';

有没有办法用完整的外部连接来做到这一点？

小提琴

postgresql join
Martin Hope
AndreKR
Asked: 2022-04-16 09:19:54 +0800 CST
  • 0

假设我有一张包含应该经过几个步骤的图像的表格：

CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool);
    
INSERT INTO images (filename, extracted, cropped, resized)
VALUES
    ('foo', false, false, false),
    ('bar', true, false, false),
    ('baz', true, true, false),
    ('qux', true, true, true);

在某些时候，我有一个查询来查找所有被裁剪但仍需要调整大小的图像：

SELECT count(*) FROM images WHERE cropped AND NOT resized;

现在我相信使查询快速的最好方法是部分索引：

CREATE INDEX ON images (cropped, resized) WHERE (cropped AND NOT resized);

我将其设为局部，因为这cropped AND NOT resized是一种相对罕见的状态，而可能有数百万张图像已经完全处理，还有数百万张图像尚未裁剪。

我现在的问题是，除了索引之外，我还需要统计信息吗？

其中之一？

CREATE STATISTICS stat1 (dependencies) ON cropped, resized FROM images;
CREATE STATISTICS stat2 (ndistinct) ON cropped, resized FROM images;
CREATE STATISTICS stat3 (mcv) ON cropped, resized FROM images;
ANALYZE images;

我找到了我之前错过的章节How the Planner Uses Statistics （或者更确切地说与Planner 使用的统计信息混为一谈），但它只讨论了如何将统计信息转换为行估计。鉴于显然没有关于索引的统计数据，我不清楚如何选择索引。

postgresql statistics
Martin Hope
AndreKR
Asked: 2022-04-05 14:30:17 +0800 CST
  • 3

我是这样查询的：

SELECT count(*)
FROM orders
WHERE planned_shipping_date >= '2022-04-04'
AND planned_shipping_date < '2022-04-05'

然后我遇到了这个答案，因为在更复杂的查询中它使查询更容易阅读，我重写了这样的查询：

SELECT count(*)
FROM orders
WHERE planned_shipping_date <@ daterange('2022-04-04', '2022-04-05')

我相信它们在语义上是相同的，但看看这些计划：

Aggregate  (cost=76.91..76.92 rows=1 width=8) (actual time=1.066..1.068 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..69.73 rows=2872 width=0) (actual time=0.067..0.646 rows=2813 loops=1)
        Index Cond: ((planned_shipping_date >= '2022-04-04'::date) AND (planned_shipping_date < '2022-04-05'::date))
        Heap Fetches: 0

Aggregate  (cost=2753.57..2753.58 rows=1 width=8) (actual time=18.309..18.311 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..2751.93 rows=655 width=0) (actual time=17.520..18.132 rows=2813 loops=1)
        Filter: (planned_shipping_date <@ '[2022-04-04,2022-04-05)'::daterange)
        Rows Removed by Filter: 128138
        Heap Fetches: 0

日期范围的使用似乎排除了索引的使用。

我需要不同的索引还是不应该使用这样的日期范围？

postgresql index
Martin Hope
AndreKR
Asked: 2021-05-09 14:25:04 +0800 CST
  • 3

例如，如果我有一个name带有排序规则的文本列tr-TR，并且我运行一个查询

SELECT * FROM t WHERE name LIKE 'a%'

然后这将进行 seq 扫描。

如果我现在创建一个索引

CREATE INDEX ON t(name text_pattern_ops)

上面的查询会变成位图扫描。但是还有另一种技术可以达到相同的结果：

CREATE INDEX ON t(name COLLATE "C")

这些方法是完全等效的还是有区别的？

postgresql index
Martin Hope
AndreKR
Asked: 2021-04-25 08:47:06 +0800 CST
  • 2

我想从时间戳中添加（或更确切地说是减去）天数。

例如，2020-04-13 00:00:00 Europe/Berlin我想减去 20 天，我希望结果是2020-03-24 00:00:00 Europe/Berlin.

如果我跑

select ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - INTERVAL '20 DAY') AT TIME ZONE 'Europe/Berlin'

我明白了2020-03-23 23:00:00.000000。

这似乎是因为 PostgreSQL 减去 20 * 24 hours （在该日期范围内发生 DST 切换），这与减去 20 days不同。

postgresql date
Martin Hope
AndreKR
Asked: 2019-10-10 04:05:39 +0800 CST
  • 4

我想设置一个 CHECK 约束，以确保text[]列的元素仅包含某些值。

设置示例：

CREATE TABLE foo(
  countries text[]
);

INSERT INTO foo VALUES ('{"Morocco", "Mali", "Indonesia"}');
INSERT INTO foo VALUES ('{"Sokovia", "Mali"}');

现在应该只允许“摩洛哥”、“马里”和“印度尼西亚”，所以第二行应该被约束拒绝。

我有一个“有效”的解决方案：

CHECK (array_length(
  array_remove(
    array_remove(
      array_remove(
        countries,
        'Indonesia'
      ), 'Mali'
    ), 'Morocco'
  ), 1) IS NULL)

但这不是很可读。

我也试过这个：

CHECK ((
  SELECT unnest(countries)
  EXCEPT
  SELECT unnest(array['Morocco', 'Mali', 'Indonesia'])
) IS NULL)

但：

错误不能在检查约束中使用子查询

postgresql array
Martin Hope
AndreKR
Asked: 2019-04-10 07:56:08 +0800 CST
  • 1

我to_regclass()用来检查表是否存在（从这里）。这适用于小写表名，但对于带有大写字母的表名，它总是返回 NULL，即使表存在：

CREATE TABLE "asdf" (s text);
CREATE TABLE "FooBar" (s text);

SELECT to_regclass('asdf'); --     -> "asdf"
SELECT to_regclass('FooBar'); --   -> NULL
SELECT to_regclass('foobar'); --   -> NULL

小提琴

postgresql
Martin Hope
AndreKR
Asked: 2018-06-20 02:49:25 +0800 CST
  • 3

这是来自 Postgres 9.6 的 EXPLAIN ANALYZE 的一部分：

->  Bitmap Heap Scan on cities  (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1)
       Recheck Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'ä'::text, 'ae'::text), 'ö'::text, 'oe'::text), 'ü'::text, 'ue'::text), 'ß'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)
      Rows Removed by Index Recheck: 567
      Heap Blocks: exact=497
      ->  Bitmap Index Scan on city_lookup_index  (cost=0.00..89.98 rows=265 width=0) (actual time=4.229..4.229 rows=584 loops=1)
             Index Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'ä'::text, 'ae'::text), 'ö'::text, 'oe'::text), 'ü'::text, 'ue'::text), 'ß'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)

是什么Rows Removed by Index Recheck意思？

或者换句话说：位图是无损的（只有exact页面），那么为什么它包含被重新检查（大概）删除的元组指针？

postgresql execution-plan
Martin Hope
AndreKR
Asked: 2018-06-07 05:29:27 +0800 CST
  • 2

这个问题有很多几乎重复的地方，到目前为止我发现的都是错误的和/或不完整的，所以我觉得我们需要一个规范的答案。

在 PostgreSQL 中，如何将数据库的所有权限授予用户，而不授予他们 SUPERUSER 权限？

这包括所有表、序列、类型、触发器、索引、过程和当前在数据库中的任何其他对象，以及稍后由该用户或其他人在数据库中创建的任何对象。当然，它还包括在数据库中创建对象的能力。

postgresql permissions
Martin Hope
AndreKR
Asked: 2016-09-02 02:29:26 +0800 CST
  • 2

我的理解：

除了对被引用表的有效引用之外，还允许具有外键的列包含 NULL 值。SQL 标准为外键定义了几种匹配模式，例如 MATCH SIMPLE 和 MATCH FULL。对于多列外键，只要外键的至少一列包含 NULL 值，MATCH SIMPLE 就允许任何列中存在错误（= 不引用）值。

多列外键很少见（因为多列主键是），但我可以想象有效的用例。例如，博客文章的翻译可能有一个主键(post_id, language_id)。现在，引用此类翻译的内容（例如用户当前正在编辑的翻译）将具有多列外键。

我不明白的是：

为什么我会使用 MATCH SIMPLE 作为我的外键？

在上面的示例中，有一个仅引用 bypost_id而不是 by的“当前正在编辑”条目是没有意义的language_id。

什么是有意义的例子？

foreign-key constraint