PostgreSQL query to select records which a specific value doesn't include in text array-CodePudding

I have a table like this

| id            | data                |
|---------------|---------------------|
| org:abc:basic | {org,org:abc:basic} |
| org:xyz:basic | {org,basic}         |
| org:efg:basic | {org}               |

I need to write a query to select all the rows which doesn't have the id inside the data column.

Or at least I need to query all the records which doesn't have a text starting from org: and ending with :basic within data. Currently for this I try to run

SELECT * FROM t_permission WHERE 'org:%:basic' NOT LIKE ANY (data)

query which returns everything even the first row.

CodePudding user response：

you can use the <> operator with ALL against the array:

select *
from the_table
where id <> all(data);