425nesp's questions

我在 Postgres 中有一些这样的表。

create table foo (
    txt text
);
insert into foo (txt) values ('a');
insert into foo (txt) values ('b');
insert into foo (txt) values ('c');

create table bar (
  txt text,
  n integer not null unique
);

我正在进行架构迁移。我想将数据foo从bar. 我想bar看起来像这样。

=# select * from new;
txt | n
----+--
a   | 1
b   | 2
c   | 3

n不必是连续的，但它必须是uniqueand not null。

我认为这个查询会起作用。

insert into bar (txt, n)
select txt, generate_series(1, (select count(*) from foo))
from foo;

但我认为这会一遍又一遍地为 each 生成第 1-N 行'a', 'b', 'c'，这违反了unique约束。

SQL 小提琴：http ://sqlfiddle.com/#!17/06e92c

更新

好的，我找到了一种 hacky 方法。

insert into bar (txt, n)
select txt, trunc(random() * 999999 + 1)
from foo;

当然，这不保证是unique. :/ 但是，如果数据集足够小，你也许可以勉强凑合。

另外，我理解为什么第一种方法现在失败了。generate_series返回多行，而random我只返回 1 行。

假设我有这个模式。

create table foo_access (
    id         serial primary key,
    user_id    integer not null,
    created_at timestamptz not null
);

-- This data set uses user_id=0 to mean null. _shrug_
insert into foo_access (user_id, created_at) values (0, '2020-03-20T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-19T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-18T00:00:00Z');
insert into foo_access (user_id, created_at) values (2, '2020-03-18T00:00:00Z');

我想看看有多少不同的用户在过去 5 天内每天至少访问一次 foo。

我有一个这样的查询。

select
  date_trunc('day', created_at) as period,
  count(distinct user_id) as n
from foo_access
where user_id > 0 and
  created_at >= now() - interval '5 day'
group by period

这给了我一张这样的桌子。

         period         | n
------------------------+---
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(2 rows)

几乎是我想要的，但不完全是。

有没有办法也显示没有人访问 foo 的日子？我希望桌子看起来更像这样。

         period         | n
------------------------+---
 2020-03-15T00:00:00+00 | 0
 2020-03-16T00:00:00+00 | 0
 2020-03-17T00:00:00+00 | 0
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(5 rows)

SQL小提琴：http ://sqlfiddle.com/#!17/ece05/2/0

这个问题有点类似于How to count matching values and print 0 for non-matching value in PostgreSQL? ，除了我没有加入两个表。我只有一张桌子。

我有一个包含多个用户的数据库：foo、bar和super. 我正在寻找pg_hba.conf锁定访问权限。

我想允许foo并bar能够通过密码身份验证从任何 IP 进行连接。或在本地使用信任身份验证。

我想允许super只能使用信任身份验证在本地登录。永远不要来自非本地地址。

我从这个开始。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow everything if you're on the same machine.
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

但是，后来我被卡住了，因为我不确定如何说“所有用户，除了超级用户”。我正在寻找这样的东西。

host    all             not_super       all                     md5

我认为我可以采取的一种方法是foo明确bar列出。但是，我想知道是否还有其他方法。

host    all             foo             all                     md5
host    all             bar             all                     md5

假设我有一张这样的桌子。

create table foo (
    id serial primary key,
    kind smallint not null check (kind >= 0 and kind < 3)
);

有了这个检查约束，Postgres 将只允许kind在 0 和 2 之间的 int，包括 0 和 2。所以这样的事情是有效的。

id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2
6  | 2

但是有没有办法说“最多 1 行可以有值 2”？

time=1
id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2

time=2
id | kind
---------
1  | 0
2  | 2
3  | 1
4  | 0
5  | 0

time=3
id | kind
---------
1  | 0
2  | 1
3  | 1
4  | 0
5  | 0

应该允许不同的行在kind=2不同的时间点有，但最多可以有 1 行kind=2。

请注意，其他值应不受限制。您可以拥有任意数量的kind=0s 和kind=1s。

我正在尝试计算我的应用程序中每个停车场的汽车数量以及其他一些信息。但是，我不太确定如何处理这个问题。

表

我有 3 张桌子。

• lot- 此表包含停车场的信息
• manager- 此表包含停车场经理的信息
• car- 这个表有一个可以在给定停车场的汽车列表

lot
 id | name | manager_id
----+------+------------
  1 | foo  | 1
  2 | bar  | 1

manager
 id | name
----+--------
  1 | alice
  2 | bob

car
 id | lot_id
----+--------
  1 | 1
  2 | 1
  3 | 2
  4 | 2

期望的输出

给定 a manager_id，我想要一个列表：

• 他们管理的地段的名称
• 经理的名字
• 该地段的汽车数量

这就是我想要结束的。

 id | lot_name | manager_id | manager_name | num_cars
----+----------+------------+--------------+----------
  1 | foo      | 1          | alice        | 2
  2 | bar      | 1          | alice        | 2

我已经想出如何在结果中获取经理姓名，但我不确定如何获取每个批次的汽车数量。

这是我到目前为止所拥有的。

-- manager_id 1 is alice.

select
    id, name as lot_name, manager_id,
    (select name from manager where id = 1) as manager_name
from lot where manager_id = 1;

我在想我需要在某个地方加入一个表来引入car表并以某种方式做一个count. 不完全确定如何。