CODEWITHSUNDEEP
sqlarrayspostgresql
hitchnsmile
hitchnsmile

Reputation: 61

How to select in Postgresql difference between arrays in rows?

I have a table

CREATE TABLE people
(
  id bigserial NOT NULL,
  name text
  hobbies text[]
)

Data here gets inserted each day.

Now I have few rows. And I want to get the difference in hobbies arrays between rows.

E.g.

id |name | hobbies
1   Tom    [fishing, hunting, football]
2   James  [fishing, jogging]
3   Dave   [football, computer games]

I would compare 1st row with 2nd and 2nd row with 3rd (what's inside 1st row that's not in 2nd). So the result would be:

hobbies
[hunting, football, fishing, jogging]

I want to retrieve the array of differences inside the array.

Upvotes: 0

Views: 1650

Answers (1)

Marth
Marth

Reputation: 24812

If you want to have a row per row diff, you can use a combination of EXCEPT and LEAD():

SELECT id, ARRAY_AGG(diff_hobbies)
FROM
(
  SELECT id, unnest(hobbies)
  FROM people
  EXCEPT
  SELECT id, unnest(LEAD(hobbies) OVER (ORDER BY id))
  FROM people
) t(id, diff_hobbies)
GROUP BY id
;
┌────┬─────────────────────────────┐
│ id │          array_agg          │
├────┼─────────────────────────────┤
│  1 │ {football,hunting}          │
│  2 │ {jogging,fishing}           │
│  3 │ {football,"computer games"} │ 
└────┴─────────────────────────────┘
(3 rows)

Upvotes: 2

Related Questions