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

Question

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.

user330315 · Accepted Answer · 2022-11-02 07:43:38Z

1

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

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

answered Nov 2, 2022 at 7:43

user330315

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

2 Comments

Thidasa Pankaja Over a year ago

This works. This and this select * from the_table where not id = any(data); and select * from the_table where id != all(data); are the same ? Because this also returns the same.

user330315 Over a year ago

<> is the "not equals" operator in standard SQL. Postgres also accepts the non-standard != operator which does exactly the same.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related