Postgres:如何在数据库中查找缺席的用户 [英] Postgres: how to find absent users in DB

问题描述

需要比较两个数据库时的常规任务.Somene 向我发送了用户列表，该列表应该在我的数据库中.127 个职位.

Regular tasks when need to compare two db. Somene has sent me list of users, that should be in my database. 127 positions.

当我使用 SELECT * FROM users WHERE id IN (...list of sent ids) 时，我发现只有 100 个用户.

When I make SELECT * FROM users WHERE id IN (...list of sent ids) I find only 100 users.

如何选择那些在列表中但不在我的数据库中的人?

How do I select those, who are in the list, but not in my database?

我正在尝试这样的事情:

I've trying something like this:

    SELECT id FROM
  (SELECT '089477','089485','089417','089419','089416','089415','082513','087201','084769','083467','089498','089394','085097','084818','089497','085208','082924','087204','087257','084708','0844187','089119','088475','089448','084824','089436','085200','086431','089444','089479','089486','089460','089442','089449','089413','089420','084917','084702','089433','089437','089443','081804','088813','089480','089441','087184','081806','089435','081784','089401','089434','089423','089384','089422','089382','089476','089473','089406','089461','089404','089409','089410','089412','089411','089396','089006','089381','089379','089378','089397','089405','080006','089293','089478','084846','085210','089453','089400','089452','089389','089383','089456','089402','089394','089418','089392','089387','089399','089101','089117','080163','086021','081059','089414','089108','089288','089447','089446','089388','089445','089386','089430','088828','088375','089407','083429','088645','089377','089342','089337','089332','081635','089426','087197','089425','087767','088395','089341','089349','082114','082123','084687','089333','089297','087371','089331') as all_users

WHERE id NOT IN(
SELECT trader_systems_id as id FROM users WHERE
  trader_systems_id IN (
    '089477','089485','089417','089419','089416','089415','082513','087201','084769','083467','089498','089394','085097','084818','089497','085208','082924','087204','087257','084708','0844187','089119','088475','089448','084824','089436','085200','086431','089444','089479','089486','089460','089442','089449','089413','089420','084917','084702','089433','089437','089443','081804','088813','089480','089441','087184','081806','089435','081784','089401','089434','089423','089384','089422','089382','089476','089473','089406','089461','089404','089409','089410','089412','089411','089396','089006','089381','089379','089378','089397','089405','080006','089293','089478','084846','085210','089453','089400','089452','089389','089383','089456','089402','089394','089418','089392','089387','089399','089101','089117','080163','086021','081059','089414','089108','089288','089447','089446','089388','089445','089386','089430','088828','088375','089407','083429','088645','089377','089342','089337','089332','081635','089426','087197','089425','087767','088395','089341','089349','082114','082123','084687','089333','089297','087371','089331'
  )
)

但没有运气猜测.

推荐答案

您可以对值列表使用外连接:

You can use an outer join to a values list:

SELECT t.id 
FROM (
  values 
     ('089477'),('089485'),('089417'),('089419'),('089416'),('089415'),('082513'),('087201'),('084769'),('083467'),('089498'),
     ('089394'),('085097'),('084818'),('089497'),('085208'),('082924'),('087204'),('087257'),('084708'),('0844187'),('089119'),
     ('088475'),('089448'),('084824'),('089436'),('085200'),('086431'),('089444'),('089479'),('089486'),('089460'),('089442'),
     ('089449'),('089413'),('089420'),('084917'),('084702'),('089433'),('089437'),('089443'),('081804'),('088813'),('089480'),
     ('089441'),('087184'),('081806'),('089435'),('081784'),('089401'),('089434'),('089423'),('089384'),('089422'),('089382'),
     ('089476'),('089473'),('089406'),('089461'),('089404'),('089409'),('089410'),('089412'),('089411'),('089396'),('089006'),
     ('089381'),('089379'),('089378'),('089397'),('089405'),('080006'),('089293'),('089478'),('084846'),('085210'),('089453'),
     ('089400'),('089452'),('089389'),('089383'),('089456'),('089402'),('089394'),('089418'),('089392'),('089387'),('089399'),
     ('089101'),('089117'),('080163'),('086021'),('081059'),('089414'),('089108'),('089288'),('089447'),('089446'),('089388'),
     ('089445'),('089386'),('089430'),('088828'),('088375'),('089407'),('083429'),('088645'),('089377'),('089342'),('089337'),
     ('089332'),('081635'),('089426'),('087197'),('089425'),('087767'),('088395'),('089341'),('089349'),('082114'),('082123'),
     ('084687'),('089333'),('089297'),('087371'),('089331')
) as t(id)
  left join users u on t.id = u.trader_systems_id 
where u.trader_systems_id  is null;

您还可以返回找到和未找到的用户:

You can also return found and not found users:

select t.id, 
       u.*,
from (
   values ( .... )
) as t(id)
  left join users u on t.id = u.trader_systems_id;

<小时>

如果你得到一个用逗号分隔的 ID 的字符串，你可以使用 unnest() 和 string_to_array() 把它变成一个合适的集合:

---

If you get a single string with comma separated IDs, you can use unnest() and string_to_array() to turn that in a proper set:

select t.id, 
       u.*,
from unnest(string_to_array('089477,089485,089417,089419,089416,..', ',',  null)) as t(id)
  left join users u on t.id = u.trader_systems_id;

这篇关于Postgres:如何在数据库中查找缺席的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！