Project

General

Profile

« Previous | Next » 

Revision 10245

Added by Aaron Marcuse-Kubitza over 11 years ago

bugfix: inputs/*/*/postprocess.sql: made all operations idempotent, so that postprocess.sql can be run repeatedly (e.g. by new-style import)

View differences:

inputs/.geoscrub/county_centroids/postprocess.sql
1 
ALTER TABLE :table ALTER COLUMN latitude TYPE double precision
2 
USING latitude::double precision;
3 
ALTER TABLE :table ALTER COLUMN longitude TYPE double precision
4 
USING longitude::double precision;
5 
ALTER TABLE :table ALTER COLUMN error_km TYPE double precision
6 
USING error_km::double precision;
1 
SELECT util.search_path_append('util');
2 

  		




  
  3
  
    
SELECT set_col_types(:table_str, ARRAY[


  		




  
  4
  
    
  ('latitude', 'double precision')


  		




  
  5
  
    
, ('longitude', 'double precision')


  		




  
  6
  
    
, ('error_km', 'double precision')


  		




  
  7
  
    
]::col_cast[]);


  		












  

    
      inputs/BRIT/specimen_flat/postprocess.sql
    
  





  1
  
  
    
ALTER TABLE :table ADD PRIMARY KEY (specimen_id);


  		




  
  1
  
    
SELECT util.search_path_append('util');


  		




  
  2
  
    

  		




  
  3
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY (specimen_id)$$);


  		












  

    
      inputs/newWorld/iso_code_gadm/postprocess.sql
    
  





  1
  
  
    
ALTER TABLE :table ADD UNIQUE ("GADM country", "2-digit iso code");


  		




  
  1
  
    
SELECT util.search_path_append('util');


  		




  
  2
  
    

  		




  
  3
  
    
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD CONSTRAINT "iso_code_gadm.unique" UNIQUE ("GADM country", "2-digit iso code")$$);


  		












  

    
      inputs/SpeciesLink/Specimen/postprocess.sql
    
  





  1
  
  
    
CREATE INDEX ON :table ("dwc_terms_institutionCode");


  		




  
  1
  
    
SELECT util.search_path_append('util');


  		




  2
  2
  
    

  		




  
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym" ON $$||:table_str||$$ ("dwc_terms_institutionCode")$$);


  		




  
  4
  
    

  		




  3
  5
  
    
-- Remove institutions that we have direct data for


  		




  4
  6
  
    
DELETE FROM :table


  		




  5
  7
  
    
WHERE "dwc_terms_institutionCode" IN (


  		












  

    
      inputs/REMIB/Specimen/postprocess.sql
    
  





  1
  
  
    
CREATE INDEX ON :table (acronym);


  		




  
  1
  
    
SELECT util.search_path_append('util');


  		




  2
  2
  
    

  		




  
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym" ON $$||:table_str||$$ (acronym)$$);


  		




  
  4
  
    

  		




  3
  5
  
    
-- Remove institutions that we have direct data for


  		




  4
  6
  
    
DELETE FROM :table


  		




  5
  7
  
    
WHERE acronym IN (


  		












Also available in:  Unified diff