Project

General

Profile

metacat / src / LTER_DOI_conversion.sql @ 8556

1
-- on KNB, we copy this to the file to transfer to LTER
2
-- COPY identifier TO '/tmp/knb_identifier.csv' WITH CSV HEADER;
3
-- scp to LTER
4

    
5
/**
6
 * LTER begin here
7
 */
8
-- create the table to house the information
9
CREATE TABLE knb_identifier AS SELECT * FROM identifier WHERE false;
10
-- on LTER, we load this
11
COPY knb_identifier FROM '/tmp/knb_identifier.csv' WITH CSV HEADER;
12
-- remove the non-doi entries since we don't need them for the update
13
DELETE knb_identifier WHERE guid NOT LIKE 'doi%';
14

    
15
-- update the guids on LTER
16
BEGIN;
17

    
18
UPDATE identifier lter_id
19
SET guid = knb_id.guid
20
FROM knb_identifier knb_id
21
WHERE knb_id.docid = lter_id.docid
22
AND knb_id.rev = lter_id.rev;
23

    
24
COMMIT;
25

    
26
-- clean up
27
DROP TABLE knb_identifier;