Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table I have a query similar to this below. As you can see, there are multiple parts which are very similar.

SELECT 
    id, 
    aaaa, 
    bbbb,
    tags
    ,(
        SELECT COUNT(*) &gt; 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags &amp; 1 &lt;&gt; 0
    ) as "has_children_tag_1"
    ,(
        SELECT COUNT(*) &gt; 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags &amp; 2 &lt;&gt; 0
    ) as "has_children_tag_2"
    ,(
        SELECT COUNT(*) &gt; 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags &amp; 4 &lt;&gt; 0
    ) as "has_children_tag_3"
    ,(
        SELECT COUNT(*) &gt; 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags &amp; 8 &lt;&gt; 0
    ) as "has_children_tag_4"
FROM A
LEFT JOIN Z
    ON A.id = Z.id


I know one thing, that I cannot split flag column into multiple bit columns, so I have to figure out how to query my existing tables in efficient way.

I am wondering is it possible (I don't know how to do that) to create some kind of temporary table for part:

    SELECT COUNT(*) &gt; 0
    FROM A
    LEFT JOIN B 
        ON A.id = B.parent_id
    WHERE A.buzz=C.buzz


to have a possibility to run on this tmp table WHERE B.tags &amp; 1 &lt;&gt; 0, WHERE B.tags &amp; 2 &lt;&gt; 0, etc.

Any help/suggestion will be appreciated.
You could merge the subqueries using this model:

SELECT bool_or(B.tags&amp;1&lt;&gt;0) as "has_children_tag_1",
       bool_or(B.tags&amp;2&lt;&gt;0) as "has_children_tag_2",
       bool_or(B.tags&amp;4&lt;&gt;0) as "has_children_tag_3",
       bool_or(B.tags&amp;8&lt;&gt;0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions]


bool_or is an aggregate function that evaluates to true if at least one value is true, so that should lead to the same results as the multiple count(*)&gt;0 in the question, except as one single subquery.

To push these columns into an otherwise unrelated query, you may add the above subquery as a CTE in a WITH clause, and just put it in the FROM list. For instance, modifying your original query:

WITH flags AS
(SELECT bool_or(B.tags&amp;1&lt;&gt;0) as "has_children_tag_1",
       bool_or(B.tags&amp;2&lt;&gt;0) as "has_children_tag_2",
       bool_or(B.tags&amp;4&lt;&gt;0) as "has_children_tag_3",
       bool_or(B.tags&amp;8&lt;&gt;0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions] )
SELECT 
  id, 
  aaaa, 
  bbbb,
  tags,
  has_children_tag_1,
  has_children_tag_2,
  has_children_tag_3,
  has_children_tag_4
FROM flags,A
LEFT JOIN Z
    ON A.id = Z.id