How to get fields with specific array size in postgresql

Question

I have a array int[] field in my table and want to get only these fields who have int[] field size >= 3 and <= 5.

I tried something like this :

SELECT cadinality(tt.numbers) >= 3, tt.customer_id 
FROM table_table tt 
LIMIT 50

but this return weird column with with checkmark, but it should return fields tt.numbers (only with size => 3 and <= 5).

please add some sample data and expected output

S-Man
– S-Man

2019-10-08 12:49:39 +00:00
Commented Oct 8, 2019 at 12:49 — S-Man
– S-Man, Commented Oct 8, 2019 at 12:49

user330315 · Accepted Answer · 2019-10-08 13:13:07Z

1

Your query returns all rows from your table, but instead of returning the numbers column you are returning a boolean expression that shows if the cardinality of the numbers column is bigger than 2.

If you want to limit the number of rows, you need a WHERE clause:

SELECT tt.numbers, tt.customer_id 
FROM table_table tt 
WHERE cadinality(tt.numbers) between 3 and 5
LIMIT 50;

cadinality(tt.numbers) between 3 and 5

is a shorthand notation for

cadinality(tt.numbers) >= 3 and cadinality(tt.numbers) <= 5

answered Oct 8, 2019 at 13:13

user330315

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

Collectives™ on Stack Overflow

How to get fields with specific array size in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related