时间戳

首页 > 解决方案 > Postgresql左连接问题

sql - Postgresql左连接问题

问题描述

我有两张桌子

create table sources (
    user_id bigint,
    source varchar,
    created timestamp default now(),
    unique(user_id, source)
)

create table subscriptions (
    user_id bigint unique primary key,
    plan int not null references plans(id),
    starts timestamp default now(),
    ends timestamp default now() + interval '30' day
)

当用户有活动订阅时，我尝试从源中选择所有内容，我使用此查询

SELECT src.* FROM sources AS src LEFT JOIN subscriptions as sub ON sub.user_id=src.user_id WHERE now() < sub.ends

但是，它并没有返回所有数据，当我尝试时问题变得清晰

SELECT * FROM sources AS src LEFT JOIN subscriptions as sub ON sub.user_id=src.user_id

可能是什么问题以及我如何以其他方式获取此信息？PS 订阅表具有其他 user_id 的行。非常感谢你！ 第二个sql查询的输出

标签： sqlpostgresql

解决方案

更新

你left join的工作。如果您的图像是，SELECT * FROM sources AS src LEFT JOIN subscriptions as sub ON sub.user_id=src.user_id那么您的一些用户没有订阅。

SELECT * FROM sources AS src LEFT JOIN subscriptions ...only 确保sources将获取 in 中的每个匹配行，而不管它是否在subscriptions.

[当我认为这是一个不同的问题时，我写了其余的，但建议仍然存在。]

这是可能的，因为sources.user_id没有设置为外键，因此数据库无法强制引用完整性。就数据库而言，sources.user_id只是一些数字。如果您删除一个用户，他们的来源和订阅将一直存在。

一般来说...

  • 聪明的主键只会带来复杂性。给出比简单连接表更复杂的所有内容id bigserial primary key并完成它。
  • 声明一切not null，除非你有充分的理由它应该为空。
  • 用于on delete cascade在删除用​​户时进行清理，但请参见下文。
create table sources (
    id bigserial primary key,

    -- When a user is deleted, its sources will also be deleted
    user_id bigint not null references users(id) on delete cascade,

    source varchar not null,
    created timestamp not null default now(),

    unique(user_id, source)
)

create table subscriptions (
    id bigserial primary key,

    -- When a user is deleted, its subscriptions will also be deleted
    user_id bigint not null unique references foreign key users(id) on delete cascade,

    -- Make foreign key names consistent, and use a consistent type for ids.
    plan_id bigint not null references plans(id) on delete cascade,

    starts timestamp not null default now(),
    ends timestamp not null default now() + interval '30' day
)

当已删除的用户仍有来源和订阅时是否使用on delete cascade取决于您要如何处理这种情况。您可能希望阻止删除仍然具有活动源和子的用户，在这种情况下，请停止on delete cascade并手动清理用户的源和子。

推荐阅读