Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table
0
votes
1
answer
458
views
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(*) > 0
FROM A
LEFT JOIN B
ON A.id = B.parent_id
WHERE A.buzz=C.buzz
AND B.tags & 1 0
) as "has_children_tag_1"
,(
SELECT COUNT(*) > 0
FROM A
LEFT JOIN B
ON A.id = B.parent_id
WHERE A.buzz=C.buzz
AND B.tags & 2 0
) as "has_children_tag_2"
,(
SELECT COUNT(*) > 0
FROM A
LEFT JOIN B
ON A.id = B.parent_id
WHERE A.buzz=C.buzz
AND B.tags & 4 0
) as "has_children_tag_3"
,(
SELECT COUNT(*) > 0
FROM A
LEFT JOIN B
ON A.id = B.parent_id
WHERE A.buzz=C.buzz
AND B.tags & 8 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(*) > 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 & 1 0, WHERE B.tags & 2 0, etc.
Any help/suggestion will be appreciated.
Asked by noisy
(109 rep)
Jun 22, 2016, 02:55 PM
Last activity: Jun 22, 2016, 11:34 PM
Last activity: Jun 22, 2016, 11:34 PM