Get rows of parents without children-CodePudding

I have this table:

UID	CHILD_UID
a	{}
b	{}
c	{a}

UID is a VARCHAR and CHILD_UID is a VARCHAR[]. I need a query that returns b,c. I mean a list of parents without children, checking that the uid is not in any of the child_uid rows.

a is not a parent, is a child of c.
b is a parent.
c is a parent.

I have tried this but with no results:

SELECT * 
FROM table 
WHERE NOT uid = ANY(SELECT child_uid FROM table);

In words, get the items that its uid is not in the child_uid column.

CodePudding user response：

child_uid is an array, so it should be converted to a list of childs (using UNNEST)

SELECT * FROM table 
   WHERE NOT uid = ANY(SELECT unnest(child_uid) FROM table);

CodePudding user response：

You can use a LEFT SELF JOIN where you check whether the parent is not found as a child:

SELECT parents.UID
FROM       tab parents
LEFT JOIN tab children
       ON parents.UID = ANY(children.CHILD_UID)
WHERE children.UID IS NULL

Try it here.