Enabling EXCEPT keyword in pg-mem

307 Views Asked by At

I have a use-case where I run a Javascript lambda that uses SQL queries to update tables on our system. One of the queries looks like this (table names and joining predicates obfuscated for privacy):

(
     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

Our lambdas are tested using pg-mem. Unfortunately, it doesn't look like the framework allows for the EXCEPT keyword, as witnessed from the following error:

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
.
.
.

While pg-mem DOES allow for registering some missing SQL functions, such as TRIM or now(), which you can do like this:

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()
});

it's not clear whether something similar can be done with basic SQL operators.

I asked in their GH yesterday but the community is not as big as SO (obviously), so I was wondering if anybody had encountered this issue before and successfully dealt with it.

0

There are 0 best solutions below