devops/postgres/upgrade/0.0.5.9.1.sql

   1 create table public.node_story_archive_history (
   2   id SERIAL,
   3   node_id INTEGER NOT NULL,
   4   ngrams_type_id INTEGER NOT NULL,
   5   patch jsonb DEFAULT '{}'::jsonb NOT NULL,
   6   PRIMARY KEY (id),
   7   FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE
   8 );
   9 ALTER TABLE public.node_story_archive_history OWNER TO gargantua;
  10
  11
  12 INSERT INTO node_story_archive_history (node_id, ngrams_type_id, patch) SELECT t.node_id, t.ngrams_type_id, t.patch FROM
  13 (
  14 WITH q AS (SELECT node_id, history.*, row_number() over (ORDER BY node_id) AS sid
  15 FROM node_stories,
  16 jsonb_to_recordset(archive->'history') AS history("Authors" jsonb, "Institutes" jsonb, "NgramsTerms" jsonb, "Sources" jsonb))
  17
  18 (SELECT node_id, sid, 1 AS ngrams_type_id, "Authors" AS patch FROM q WHERE "Authors" IS NOT NULL)
  19 UNION (SELECT node_id, sid, 2 AS ngrams_type_id, "Institutes" AS patch FROM q WHERE "Institutes" IS NOT NULL)
  20 UNION (SELECT node_id, sid, 4 AS ngrams_type_id, "NgramsTerms" AS patch FROM q WHERE "NgramsTerms" IS NOT NULL)
  21 UNION (SELECT node_id, sid, 3 AS ngrams_type_id, "Sources" AS patch FROM q WHERE "Sources" IS NOT NULL)
  22 ORDER BY node_id, ngrams_type_id, sid
  23 ) AS t;