Skip to content

Instantly share code, notes, and snippets.

@amackiewicz

amackiewicz/[pgsql] update ordering with given list of values

Last active May 8, 2019 10:49
Show Gist options
  • Save amackiewicz/08e2e92319e0ab6cce265dc88d66dfcd to your computer and use it in GitHub Desktop.
Save amackiewicz/08e2e92319e0ab6cce265dc88d66dfcd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
[pgsql] update ordering with given list of values
UPDATE
media
SET
"index" = s.ordering::INT
, "updated_at" = CURRENT_TIMESTAMP
FROM (
SELECT
"unnest"
, "ordinality"
FROM
UNNEST(array['value1', 'value2', 'value3']) WITH ORDINALITY
) AS s (media_id, ordering)
WHERE
id in (s.media_id::UUID)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment