Outer joins and nullable

[FrigoEU]

Hey,

First off, thanks for this module! It's a really nice piece of work.

Something I've encountered, and I'm not sure if I'm doing wrong or it's just a shortcoming of pgtyped or postgresql itself, is the inference of "nullability" when using outer joins. For example:

create table users ( id int not null);
create table names (userid int not null, name text not null);

export const selectNames = sql<ISelectNamesQuery>`
  select users.id, names.name
  from users
    left outer join names on names.userid = users.id
`;

Pgtyped will then generate the following type definitions:

/** 'SelectNames' return type */
export interface ISelectNamesResult {
  id: number;
  name: string;
}

I would have expected the name field to be string | null. Is there something I can do to get this nullable type inferred?

[adelsz]

Hey @FrigoEU, Unfortunately there isn't much we can do about this atm, this seems to be a bug in the postgres type inference engine.

[mgreenw]

Seems like the Rust SQLX project encountered the same issue and was able to solve it (mostly?) using EXPLAIN VERBOSE:

Issue: https://github.com/launchbadge/sqlx/issues/367 PR: https://github.com/launchbadge/sqlx/pull/566

+1 on what @FrigoEU said: pgtyped is awesome! Thank you!

[FrigoEU]

This is very cool actually. I've since started using sqltyper because that project does analyze the sql itself. However, this is proving to be quite tedious (sql syntax is quite vast) so I think this solution might actually be better! I might take a stab at implementing the solution of the sqlx people in pgtyped at some point.

[adelsz]

Nullability overrides have now been implemented. Faulty postgres type inference will now be tracked in the meta issue.