CODEWITHSUNDEEP
sqlpostgresqlgosql-deletepgx
GopherGopher
GopherGopher

Reputation: 386

DELETE with ANY clause not working as expected

I'm trying to DELETE certain rows in my Postgress DB using the ANY clause.

Here's the structure -

---- TABLE A -----
-------------------
rId UUID NOT NULL,
oId UUID NOT NULL,
eId UUID NOT NULL,
--- <other fields>
PRIMARY KEY (rId, oId, eId),
CONSTRAINT fk_r FOREIGN KEY (rId) REFERENCES another_table(rId),
CONSTRAINT fk_eo FOREIGN KEY (oId, eId) REFERENCES some_other_table (oId, eId)

Suppose this table has below rows -

|  rId  | oId  |  eId  |
-----------------------
|   1   |  abc |  pqr  |
|   1   |  abc |  xyz  |
|   1   |  abc |  utd  |

I want to delete all rows from this table that have rId = 1, oId = abc AND [eId != pqr AND eid != xyz]

I write the below query -

DELETE FROM TABLE_A
WHERE   rId = 1
    AND oId = abc
    AND eId != ANY (array['pqr'::uuid, 'xyz'::uuid]);

The problem is that this is not working. Both the rows that have eId = pqr Or eId = xyz are getting deleted. How can I correct this?

Passing them one by one isn't an option (I'm getting a slice of eId which I'm passing as a param to the go function that runs this query).

Something like below -

func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
    batch := &pgx.Batch{}

    deleteQuery := `
        DELETE
        FROM table_a
        WHERE rId = $1
            AND oId = $2
            AND eId != ANY($3);
        `

    batch.Queue(deleteQuery, req.rId, req.oId, eIds)

    return batch
}

Please disregard that 1, abc, pqr are not UUIDs in this example. I kept it that way for brevity.

Upvotes: 0

Views: 785

Answers (1)

mkopriva
mkopriva

Reputation: 38203

As The Impaler points out in their comment, eId != ANY ($3) will always evaluate to true if the $3 array is a set with more than one element.

For your use case you should instead use ALL, e.g. eId != ALL ($3).

Upvotes: 1

Related Questions