Create table statement for NYC properties with 421a tax exemption status, using data from https://github.com/toolness/nyc-421a-xls

nyc_421a.sql

DROP TABLE IF EXISTS nyc_421a;

CREATE TABLE nyc_421a (
    years numeric,
    borough_name varchar,
    borough numeric,
    neighborhood varchar,
    building_class_category varchar,
    tax_class_at_present varchar,
    block numeric,
    lot numeric,
    building_class_at_present varchar,
    address varchar,
    zipcode numeric,
    residential_units numeric,
    commercial_units numeric,
    total_units numeric,
    land_sq_ft numeric,
    gross_sq_ft numeric,
    year_built numeric
);

COPY nyc_421a 
FROM '/Users/clhenrick/projects/nyc-421a-xls/data/421a.csv'
WITH CSV HEADER;

-- correct column data types
ALTER TABLE nyc_421a 
ALTER COLUMN years type integer,
ALTER COLUMN borough type integer,
ALTER COLUMN block type integer,
ALTER COLUMN lot type integer,
ALTER COLUMN zipcode type integer,
ALTER COLUMN residential_units type integer,
ALTER COLUMN commercial_units type integer,
ALTER COLUMN total_units type integer,
ALTER COLUMN year_built type integer;

-- add column for concatenated borough, block, lot
ALTER TABLE nyc_421a
ADD COLUMN bbl varchar,
ADD CONSTRAINT bbl CHECK (char_length(bbl) <= 10);
UPDATE nyc_421a SET bbl = borough::text || LPAD(block::text, 5, '0') || LPAD(lot::text, 4, '0');