Remove overlap entries from postgres arrays

Question

I am trying to remove entries from an array field that are found in a query.

tablename.listfield::integer[] has a full list I am trying to remove a list of values from that field, which are gathered within the update query. the ARRAY_REMOVE method only accepts single values, and the intarray module which has int[] - int[] doesn't seem to be an option.

the ARRAY[] && ARRAY[] can boolean return if there is overlap, but that doesn't help me

basically what I need is a real working version of this concept, which I know does not work.

UDPATE tablename SET listfield = ARRAY_REMOVE( listfield, ( select id from othertable ) )

is it possible to get this done with maybe a tricky CTE setup or something?

thanks!

Nick Barnes · Accepted Answer · 2016-03-23 21:27:43Z

2

I'm not sure why you say intarray doesn't seem to be an option, because it works just fine:

... SET listfield = listfield - ( SELECT array_agg(id) FROM othertable )

But if you want to do this without installing the extension, you can UNNEST the array and use the EXCEPT construct:

... SET listfield = ARRAY(SELECT UNNEST(listfield) EXCEPT SELECT id FROM othertable)

answered Mar 23, 2016 at 21:27

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gareth Pursehouse Over a year ago

fantastic thanks! looks like that's working exactly right

Collectives™ on Stack Overflow

Remove overlap entries from postgres arrays

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related