leftouterjoin.sh

#!/usr/bin/env bash

# # create target database
# psql -d bird -q -c "
# DROP TABLE grid;
# CREATE TABLE grid
# (
#  gid integer
# ) WITH (OIDS=FALSE);
# "
# psql -d bird -q -c "
# COPY grid FROM '/tmp/csv/grid.csv' WITH DELIMITER AS ',' CSV HEADER;
# "
# 

for c in `ls * | grep geoc`
do
    csvname=`basename ${c} .csv`
    column=`echo ${csvname} | awk -F'geoc' '{print $2}'`

# create database
psql -d bird -q -c "
DROP TABLE IF EXISTS ${csvname};
CREATE TABLE ${csvname}
(
   gid integer, 
   "islnd_Cont" integer, 
   c${column} character, 
   c${column}b integer, 
   c${column}w integer
) WITH (OIDS=FALSE)
;"
# import data
psql -d bird -q -c "
COPY ${csvname} FROM '/tmp/csv/${csvname}.csv' WITH DELIMITER AS ',' CSV HEADER;"

done

echo "Input the order name"
read birdorder

# create a temp_grid table first for left outer join
psql -d bird -q -c "
CREATE TABLE temp_grid AS SELECT gid FROM grid;
"
# join outer data into a single table
for c in `ls * | grep geoc`
do
    csvname=`basename ${c} .csv`
    column=`echo ${csvname} | awk -F'geoc' '{print $2}'`

# LEFT OUTER JOIN
psql -d bird -q -c "
CREATE TEMP TABLE tmp AS SELECT temp_grid.*,${csvname}.c${column},${csvname}.c${column}b,${csvname}.c${column}w FROM temp_grid
LEFT OUTER JOIN ${csvname} ON temp_grid.gid=${csvname}.gid;
DROP TABLE temp_grid;
CREATE TABLE temp_grid AS SELECT * FROM tmp;"
done

psql -d bird -q -c "
CREATE TABLE $birdorder AS SELECT * FROM temp_grid;
COPY $birdorder  TO '/tmp/$birdorder.csv' NULL AS '-9999' DELIMITER AS ',' CSV HEADER;
DROP TABLE temp_grid;"