CODEWITHSUNDEEP
sqlarrayspostgresqluniquedistinct
user23025019
user23025019

Reputation: 29

Get unique values from column of type "array"

I have this table:

| arrs |
|:----|
| {10000,10000,10000,10000} |
| {100,200,300} |
| {400,500,600} |
| {100,200,600} |

How get this result?

| uniques |
|:-------|
| 10000 |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |

Or this result?

| arrUniques |
|:----|
| {10000,100,200,300,400,500,600} |

See -> fiddle

Upvotes: 0

Views: 63

Answers (3)

Charlieface
Charlieface

Reputation: 72194

Strictly speaking it's better to place the unnest into the FROM part, as putting it into the SELECT has some weird effects in some cases.

SELECT DISTINCT
  a.value AS uniques
FROM yourTable t
CROSS JOIN LATERAL unnest(t.arrs) AS a(value);

Upvotes: 0

Arf_code
Arf_code

Reputation: 543

  1. You can use Distinct unnest to display unique results as per your first scenario :

    SELECT DISTINCT unnest(arr) AS uniques
FROM "test";

  2. For your second scenario displaying result in single array you can add array_agg:

    SELECT array_agg(DISTINCT unnest(arr)) AS arrUniques
FROM "test";

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

You should be able to use UNNEST() along with DISTINCT here:

SELECT DISTINCT UNNEST(arrs) AS uniques
FROM yourTable;

Upvotes: 2

Related Questions