PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

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

    SELECT DISTINCT UNNEST(arrs) AS uniques
FROM yourTable;
    Login or Signup to reply.
  2. 0

    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";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search