Skip to content

Instantly share code, notes, and snippets.

@robinsmidsrod robinsmidsrod/query.sql
Last active Feb 13, 2018

Embed
What would you like to do?
Trouble with large PostgreSQL statement because of unnest(ARRAY[ 'asdf', 'asdf' ])
WITH
checksums AS (
SELECT unnest(ARRAY[
-- Using quote_string_array() DBD::Pg method here
-- Example: '0000218c2a23400651a308443664d18bc0639052', ..., '000031b775731a8bbd05c873d5dfab6db49e59c7' (and 150k more of those)
$blob_ids_quoted
]) AS id
),
object_counts AS (
SELECT c.id, count(*) AS object_count
FROM checksums c JOIN object o ON c.id = o.content_checksum
GROUP BY c.id
),
history_counts AS (
SELECT c.id, count(*) AS history_count
FROM checksums c JOIN history h ON c.id = trigger_old->'content_checksum'
WHERE h.trigger_table = 'object'
GROUP BY c.id
),
combined_counts AS (
SELECT
c.id,
-- coalesce(oc.object_count,0) AS object_count,
-- coalesce(hc.history_count,0) AS history_count,
coalesce(oc.object_count,0) + coalesce(hc.history_count,0) AS ref_count
FROM checksums c
LEFT JOIN object_counts oc ON c.id = oc.id
LEFT JOIN history_counts hc ON c.id = hc.id
)
SELECT id FROM combined_counts
WHERE ref_count = 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.