Possible to make EXCEPT keyword work?

[oguimbal]

Discussed in #197

^{Originally posted by jasonfilippou March 17, 2022}
Hi,

I have the following (abstract) query that I am trying to pass to pg-mem:

(
     select DISTINCT tab1.staff_username                  as staff_id,
                     tab3.rcm_organization_code           as org_code
     from table1 tab1
              JOIN table2 tab2 ON predicate1
              JOIN table3 tab3 ON predicate2

     UNION

     select DISTINCT tab4.staff_username                  as staff_id,
                     tab5.rcm_organization_code           as org_code
     from table4 tab4
              JOIN table5 tab5 ON predicate3
              JOIN table6 tab6 ON predicate4
     WHERE NOT EXISTS(SELECT 1
                      from some_other_table
                      where some_other_predicate)
)

EXCEPT

select v.staff_id, v.organization_code
from another_table

Unfortunately, it seems as if pg-mem does not yet support EXCEPT:

Error: Error: 💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.
.
.
.
💀 Syntax error at line 22 col 17:

                  EXCEPT
                  ^
Unexpected kw_except token: "except". Instead, I was expecting to see one of the following:

    - A "kw_union" token
.
.
.

I DO know how to register functions in pg-mem and have done so for some:

pgMem.public.registerFunction({
    name: 'TRIM',
    args: [DataType.text],
    returns: DataType.text,
    implementation: str => return str != null ? str.trim() : null
});

pgMem.getSchema().registerFunction({
    name: 'now',
    returns: DataType.timestamp,
    implementation: () => moment().toISOString()
});

But for EXCEPT, I am not sure if this can be done! We are, after all, talking about functions that manipulate record sets, not strings or dates, for which registerFunction() works well. It is possible for me to implement EXCEPT in memory, but for technical reasons, we need to limit the amount of in-memory (Javascript) manipulations and instead offload everything we can to the SQL.

Any ideas? Is this possible in pg-mem? MINUS does NOT appear to be a supported pg-mem or even psql operator.