Project

General

Profile

« Previous | Next » 

Revision 57304

sequential identifiers

View differences:

import_grid_ac.sql
90 90
COPY grid_aliases       (grid_id,alias)                             FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/aliases.csv'  CSV HEADER;
91 91

  
92 92

  
93
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'openorgs____::'||md5(o.grid_id), o.name, COALESCE(t.type, 'Other'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac'  FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;
94
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'openorgs____::'||md5(grid_id), grid_id,     'grid.ac'                                              FROM grid_institutes   ) ON CONFLICT DO NOTHING;
95
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'openorgs____::'||md5(grid_id), external_id, external_id_type                                       FROM grid_external_ids ) ON CONFLICT DO NOTHING;
96
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openorgs____::'||md5(grid_id), 'en',      name                                                     FROM grid_institutes   ) ON CONFLICT DO NOTHING;
97
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openorgs____::'||md5(grid_id), iso639,    label                                                    FROM grid_labels       ) ON CONFLICT DO NOTHING;
98
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openorgs____::'||md5(grid_id), 'UNKNOWN', alias                                                    FROM grid_aliases      ) ON CONFLICT DO NOTHING;
99
INSERT INTO acronyms     (id, acronym)                               (SELECT 'openorgs____::'||md5(grid_id), acronym                                                             FROM grid_acronyms     ) ON CONFLICT DO NOTHING;
100
INSERT INTO relationships(id1, reltype, id2)                         (SELECT 'openorgs____::'||md5(grid_id), relationship_type, 'openorgs____::'||md5(related_grid_id)           FROM grid_relationships) ON CONFLICT DO NOTHING;
101
INSERT INTO urls         (id, url)                                   (SELECT 'openorgs____::'||md5(grid_id), link                                                                FROM grid_links        ) ON CONFLICT DO NOTHING;
93
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'tmp::'||md5(o.grid_id), o.name, COALESCE(t.type, 'Other'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac'  FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;
94
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'tmp::'||md5(grid_id), grid_id,     'grid.ac'                                              FROM grid_institutes   ) ON CONFLICT DO NOTHING;
95
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'tmp::'||md5(grid_id), external_id, external_id_type                                       FROM grid_external_ids ) ON CONFLICT DO NOTHING;
96
INSERT INTO other_names  (id, lang, name)                            (SELECT 'tmp::'||md5(grid_id), 'en',      name                                                     FROM grid_institutes   ) ON CONFLICT DO NOTHING;
97
INSERT INTO other_names  (id, lang, name)                            (SELECT 'tmp::'||md5(grid_id), iso639,    label                                                    FROM grid_labels       ) ON CONFLICT DO NOTHING;
98
INSERT INTO other_names  (id, lang, name)                            (SELECT 'tmp::'||md5(grid_id), 'UNKNOWN', alias                                                    FROM grid_aliases      ) ON CONFLICT DO NOTHING;
99
INSERT INTO acronyms     (id, acronym)                               (SELECT 'tmp::'||md5(grid_id), acronym                                                             FROM grid_acronyms     ) ON CONFLICT DO NOTHING;
100
INSERT INTO relationships(id1, reltype, id2)                         (SELECT 'tmp::'||md5(grid_id), relationship_type, 'tmp::'||md5(related_grid_id)                    FROM grid_relationships) ON CONFLICT DO NOTHING;
101
INSERT INTO urls         (id, url)                                   (SELECT 'tmp::'||md5(grid_id), link                                                                FROM grid_links        ) ON CONFLICT DO NOTHING;
102 102

  
103
update organizations set id = DEFAULT;

Also available in: Unified diff