Import CSV from S3 to Postgres

import-from-s3-to-postgres.sql

-- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#USER_PostgreSQL.S3Import.table_import_from_s3
-- https://github.com/chimpler/postgres-aws-s3

CREATE EXTENSION aws_s3 CASCADE;

DROP TABLE nyse;
CREATE TABLE nyse (
    exchange                VARCHAR(50),
    stock_symbol            VARCHAR(50),
    stock_date              DATE,
    stock_price_open        DECIMAL,
    stock_price_high        DECIMAL,
    stock_price_low         DECIMAL,
    stock_price_close       DECIMAL,
    stock_volume            INTEGER,
    stock_price_adj_close   DECIMAL
);

SELECT aws_s3.table_import_from_s3(
   'nyse',    
   '', -- columns
   '(FORMAT CSV, DELIMITER E''\t'', HEADER true)', 
   aws_commons.create_s3_uri('datapyro-main', 'test/NYSE-2000-2001.tsv', 'eu-west-1'), 
   aws_commons.create_aws_credentials('ACCESS_KEY', 'SECRET_KEY', '')  
);

select * from nyse limit 100;