Sig's questions

我感觉这是一个相对简单的问题，但半个小时后，我仍然不知道如何解决它。

在 PostgreSQL 查询WHERE子句中，我需要引用一个值而不是直接使用它。该查询将在 Ruby 脚本中执行，我无法直接从我的环境中插入需要获取的值。

我的查询是

CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL '?? HOURS' AND NOW()

用于计算间隔的小时数在INTERVAL_HOURS环境变量中定义。如上所述，我可以??用插值替换，因为它在单引号内。

我的想法是使用变量（或产生相同结果的东西）将插值移到单引号之外。

我发现最接近解决方案的选项是

DO $$
DECLARE myVar INT;
BEGIN
myVar := #{ENV['INTERVAL_HOURS'};
CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL 'myVar HOURS' AND NOW()
END $$;

应该可以正确插值，但会产生

Query 1 ERROR at Line 1: : ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

我尝试SELECT用替换PERFORM，但视图未创建（并且未返回任何错误）。

我这里漏掉了什么？我可以采取其他方法吗（例如，WITH子句）？

我有一个具有 type属性的punctures表，我需要插入一条开放式记录，下限为当前时间 ( )。我试过extenttsrangeextentnow()

INSERT INTO punctures(extent)
VALUES(tsrange(now(), ));

及其变体，但都会产生错误。

我如何定义tsrange？

在 PG 12 数据库中，我有reservations带有reserved_between tsrange列的表。

对于特定的analysis，我需要知道最后一次预订的结束时间（稍后），并且下面的查询就是这样做的。

SELECT MAX(upper(reservations.reserved_between))
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

识别分析所需的参数在哪里<MYID>以及在哪里。<MYTAG>

现在，如果没有reservations，我需要返回 引用的as_early_as( datetime) 值。batchanalysis

我认为COALESCE这可能是这份工作的一个不错的选择。因此，我将查询更新如下。

SELECT COALESCE(MAX(upper(reservations.reserved_between)), batches.as_early_as)
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
JOIN dev.batches ON analyses.batch_id = batches.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG> 

但是，当我跑步时，我得到

错误：列“batches.as_early_as”必须出现在 GROUP BY 子句中或用于聚合函数第 1 行：...ALESCE(MAX(upper(reservations.reserved_between)), batches.as...

而且我不确定如何解决它，并且文档对我没有太大帮助。

我应该在查询中更改什么？

我有以下 PG (12)reservations表

id               uuid
reservable_id    uuid
reserved_between tsrange    

如何确保没有 2 个相同的预订reservable_id相互重叠？

上的“简单”唯一索引reservable_id，reserved_between仅（如预期）仅在相等范围内有效。

在 SQLite DB 中，我有一个包含station整数字段和at日期字段的表。

如何结合这两个值在一个WHERE子句中获得一个at加station月的日期？

即带有at = '2019-01-01'and的记录station = 3，我的 WHERE 子句应该看起来像WHERE before <= '2019-04-01'

我在网上查看了几个资源，包括https://www.sqlite.org/lang_datefunc.html但没有人提到使用字段值来计算日期。

我注意到在订购时，PostgreSQL（我目前正在使用 11，但我认为这同样适用于 10 和 9）区分大小写。它在小写字符串之前返回大写字符串。例如，假设我有一个friends包含 3 条记录的表，first_name列分别包含 adam、Tony、Paul。

SELECT name
FROM friends
ORDER BY name

返回

[name]
 Paul
 Tony
 adam

无论大小写如何，为了让它们按字母顺序排列，我可以使用该lower函数。

SELECT name
FROM friends
ORDER BY lower(name)

返回

[name]
 adam
 Paul
 Tony

现在我需要将friends表与cities表连接起来，以便只获取与城市相关联的朋友，同时保持朋友按字母顺序排列，并且不更改他们姓名的大小写。

以下查询有效

SELECT DISTINCT lower(friends.name)
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

但是返回的名字都是小写的。但是，下面的查询

SELECT DISTINCT friends.name
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

返回错误

ORDER BY 表达式必须出现在选择列表中。

在保留返回字符串的大小写的同时应用不区分大小写的顺序的最简单方法是什么？

如何将texts 数组转换为 s 数组UUID？

我需要join在两个表之间做一个：users和projects.

该users表有一个名为的数组字段project_ids，其中包含项目 ID 作为文本。

该projects表有一个名为 的 UUID 字段id。

我最初的想法是查询如下：

SELECT * FROM projects
JOIN users ON
projects.id = ANY(users.project_ids)

但这不起作用，因为users.project_idsare not UUIDs 所以我尝试了：

projects.id = ANY(users.project_ids::uuid[])

乃至：

projects.id = ANY(ARRAY[users.project_ids]::uuid[])

但没有一个工作：

ERROR: invalid input syntax for type uuid: ""

更新

@a_horse_with_no_name 绝对正确。最好的选择应该是使用一组 UUID。

现在的问题是如何将数组更改为text数组uuid？

该users表当前为空（0 条记录）。

我努力了

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[];

这会产生

错误：列“product_ids”的 USING 子句的结果无法自动转换为类型 uuid 提示：您可能需要添加显式转换。

ALTER TABLE "users" ALTER COLUMN "product_ids" 使用 "product_ids"::UUID 设置数据类型 UUID;

我也试过

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[];

这会产生

错误：列“project_ids”的默认值不能自动转换为类型 uuid[]

该列默认设置为空数组。

我正在运行 PG 版本 10.4，project_ids目前是text[] nullable.

在 PG10 数据库中，我有一个名为的表ToDos，我在其中定义了一个jsonb名为tags.

记录看起来像

+----+-----------------+-----------------------------------------+
| ID |  Name           | Tags                                    |
+----------------------+-----------------------------------------+
| 1  | mow the lawn    |[{"name": "Saturday", "scope": "system"},|
|    |                 | {"name": "favorite", "scope": "user"}]  |
+----------------------+-----------------------------------------+     
| 2  | paint the fence |[{"name": "Monday", "scope": "system"}]  |
+----------------------+-----------------------------------------+

现在我正在尝试编写一个返回类似内容的查询

+----+-----------------+------------+-----------+
| ID |  Name           | Tag Name   | Tag Scope |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Saturday   | system    |
+----------------------+------------+-----------+
| 1  | mow the lawn    | favorite   | user      |
+----------------------+------------+-----------+     
| 2  | paint the fence | Monday     | system    |
+----------------------+------------+-----------+

但我很难分解这个jsonb领域。有任何想法吗？