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.