Compare table with array in Postgres

Question

I need to compare my array with a table.

I need to know which codes exist in the array and are missing from the table. As well as which codes exist in the table and are missing from the array.

I'm using Node+KnexJS+PostgreSQL:

const myArray = `array['10001517','1509','1524','155400X','903B','910','1009201X']`
let divergence = await app.db.raw(`
    select file, t.code as acc_missing
    from unnest(${myArray}) file full join
        table_a as t 
        on t.code LIKE file
    where t.code is null or file is NULL
    AND t.version LIKE '010'
    AND t.inst = 300
`)

const errorDivergence = divergence.rows

My current query does this, but generates the following error:

(node:26060) UnhandledPromiseRejectionWarning:
error: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

Erwin Brandstetter · Accepted Answer · 2020-03-06 04:33:00Z

Like the error message says, Postgres only supports join conditions for FULL [OUTER] JOIN that can be used on merge join or hash-join. t.code LIKE file does not qualify for that.

This is a known limitation. There just hasn't been enough interest for the rare case to motivate a fix. See:

https://www.postgresql.org/message-id/flat/20140407131705.342.75513%40wrigleys.postgresql.org

However, nothing in your questions says you'd actually need LIKE:

I need to know which codes exist in the array and are missing from the table. As well as which codes exist in the table and are missing from the array.

That would indicate equality (=) - which works fine:

SELECT file, t.code AS acc_missing
FROM   unnest('{10001517,1509,1524,155400X,903B,910,1009201X}'::text[]) file
FULL   join table_a t ON t.code = file    -- !
WHERE  t.code IS NULL
   OR  file IS NULL AND t.version LIKE '010' AND t.inst = 300;

The way you formatted the query indicates you'd want parentheses around (t.code IS NULL OR file IS NULL). AND binds before OR. The manual about operator precedence.

OTOH, the added predicates AND t.version LIKE '010' AND t.inst = 300 only make sense without parentheses. So here is a workaround implementing your original query with LEFT & RIGHT JOIN:

SELECT file, t.code AS acc_missing
FROM   unnest('{10001517,1509,1524,155400X,903B,910,1009201X}'::text[]) file
LEFT   JOIN table_a t ON t.code LIKE file
WHERE  t.code IS NULL

UNION ALL
SELECT file, t.code AS acc_missing
FROM   unnest('{10001517,1509,1524,155400X,903B,910,1009201X}'::text[]) file
RIGHT  JOIN table_a t ON t.code LIKE file
WHERE  file IS NULL AND t.version LIKE '010' AND t.inst = 300;

Or:

SELECT file, t.code AS acc_missing
FROM   unnest('{10001517,1509,1524,155400X,903B,910,1009201X}'::text[]) file
LEFT   JOIN table_a t ON t.code LIKE file
WHERE  t.code IS NULL

UNION ALL
SELECT NULL, t.code
FROM   table_a t
WHERE (t.code LIKE ANY ('{10001517,1509,1524,155400X,903B,910,1009201X}'::text[])) IS NOT TRUE;

db<>fiddle here

Collectives™ on Stack Overflow

Compare table with array in Postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related