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

PostgreSQL: Intersection within a table

I have a table in the form

Column A: String
Column B: Integer

These are the only columns and they both form the PK.

Sample data:

Column A Column B
‘abc’ 1
‘abc’ 2
‘abc’ 3
‘def’ 1
‘def’ 3
‘ghi’ 3

I need a view that gives me the intersection of column B for all values of A from the query.

I have already asked a few AIs, but without any meaningful results.

Expectation: select A, B from myView where A in (‘abc’,’def’,’ghi’)
which returns B=3 since 3 is the intersection of the values of B for all values of A from the query.

The best result would be (to be able to continue joining)

Column A Column B
‘abc’ 3
‘def’ 3
‘ghi’ 3

No extension should be necessary for this.

Thanks for your help and ideas 🙂

Tags:

2

Answers


  1. 0

    Not sure if I understood you correct, but I guess you want something like this:

    SELECT colA, colB
FROM yourTable
WHERE colB IN (
    SELECT colB
    FROM yourTable
    GROUP BY colB
    HAVING COUNT(DISTINCT colA) = (SELECT COUNT(DISTINCT colA) FROM yourTable)
);

    https://www.db-fiddle.com/f/24RniAr72kNddd995pysVb/0

    Login or Signup to reply.
  2. 0

    I think the most straighforward query to get the desired result would be

    SELECT array_agg(a), b
FROM example
GROUP BY b
HAVING array_agg(a) @> ARRAY['abc', 'def', 'ghi'];

    (online demo)

    however that returns one row with an array of all the a values for that intersected b, not multiple rows. Maybe you don’t need those though, in particular if you already know the a values you’re looking for? Otherwise you’d have to write

    SELECT a, b
FROM example
WHERE b = (
  SELECT b
  FROM example
  GROUP BY b
  HAVING array_agg(a) @> ARRAY['abc', 'def', 'ghi']
);

    (online demo)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search