daily pastebin goal
3%
SHARE
TWEET

Untitled

a guest Mar 13th, 2018 44 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Table1:
  2.         column_name        |          data_type          
  3. ---------------------------+-----------------------------
  4.  id                        | integer
  5.  name                      | text
  6.  account_id                | integer
  7.  tags                      | ARRAY {tag1,tag_2,'tag 3',...}
  8.  
  9. -- Table2:
  10.         column_name        |          data_type          
  11. ---------------------------+-----------------------------
  12.  id                        | integer
  13.  name                      | text
  14.  account_id                | integer
  15.  tags                      | ARRAY {tag1,tag_2,'tag 3',...}
  16.    
  17. -- Tags:
  18.         column_name        |          data_type          
  19. ---------------------------+-----------------------------
  20.  id                        | integer
  21.  name                      | text
  22.  account_id                | integer
  23.  source                    | enum (either 'table1' or 'table2')
  24.    
  25. CREATE table tags
  26.     AS
  27.     SELECT
  28.       DISTINCT(UNNEST(table1.tags)) AS name,
  29.       table1.account_id AS account_id,
  30.       'table1' as source FROM table1
  31.     UNION
  32.     SELECT
  33.       DISTINCT(UNNEST(table2.tags)) AS name,
  34.       table2.account_id AS account_id,
  35.       'table2' as source FROM table2;
  36. ALTER TABLE tags ADD COLUMN id SERIAL PRIMARY KEY;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top