Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

create table post (
  user_id INT,
  type_id INT
);

CREATE TABLE

insert into post values(1,1);

INSERT 0 1

insert into post values(1,3);

INSERT 0 1

insert into post values(2,1);

INSERT 0 1

insert into post values(2,2);

INSERT 0 1

insert into post values(2,3);

INSERT 0 1

insert into post values(3,1);

INSERT 0 1

insert into post values(4,4);

INSERT 0 1

insert into post values(5,1);

INSERT 0 1

insert into post values(5,1);

INSERT 0 1

insert into post values(5,3);

INSERT 0 1

SELECT DISTINCT user_id
FROM post p1
WHERE EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id = 1
)
AND EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id = 3
)
AND NOT EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id NOT IN (1,3)
)

user_id

1

5

SELECT 2

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
   COUNT(DISTINCT type_id) = 2
   AND MAX(CASE WHEN type_id IN (1,3) THEN 0 ELSE 1 END) = 0

user_id

1

5

SELECT 2

SELECT
  user_id
FROM
  (SELECT DISTINCT user_id, type_id FROM post) p
GROUP BY
  user_id
HAVING
   SUM(CASE WHEN type_id IN (1,3) THEN 1 ELSE 3 END) = 2

user_id

5

1

SELECT 2

SELECT user_id FROM post WHERE type_id = 1
INTERSECT
SELECT user_id FROM post WHERE type_id = 3
EXCEPT
SELECT user_id FROM post WHERE type_id NOT IN (1, 3)

user_id

1

5

SELECT 2

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
   COUNT(DISTINCT type_id) = 2
   AND COUNT(DISTINCT CASE WHEN type_id NOT IN (1, 3) THEN type_id END) = 0

user_id

1

5

SELECT 2