docker.postgres-init.sh Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of liquibase-dbmarlin Show documentation

This extension shares database deployment information from Liquibase to DBmarlin's timeline for context and insights into database performance.

The newest version!

#!/usr/bin/env bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
CREATE USER nyc_data_user WITH PASSWORD 'nyc_data_pass';
GRANT ALL PRIVILEGES ON DATABASE nyc_data TO nyc_data_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO nyc_data_user;

CREATE TABLE IF NOT EXISTS "rides"(
    vendor_id TEXT,
    pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    passenger_count NUMERIC,
    trip_distance NUMERIC,
    pickup_longitude  NUMERIC,
    pickup_latitude   NUMERIC,
    rate_code         INTEGER,
    dropoff_longitude NUMERIC,
    dropoff_latitude  NUMERIC,
    payment_type INTEGER,
    fare_amount NUMERIC,
    extra NUMERIC,
    mta_tax NUMERIC,
    tip_amount NUMERIC,
    tolls_amount NUMERIC,
    improvement_surcharge NUMERIC,
    total_amount NUMERIC
);
CREATE INDEX ON rides (vendor_id, pickup_datetime DESC);
CREATE INDEX ON rides (pickup_datetime DESC, vendor_id);
CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
CREATE INDEX ON rides (passenger_count, pickup_datetime DESC);

ALTER TABLE rides OWNER TO nyc_data_user;
GRANT ALL PRIVILEGES ON TABLE rides TO nyc_data_user;

CREATE TABLE IF NOT EXISTS "payment_types"(
    payment_type INTEGER,
    description TEXT
);
INSERT INTO payment_types(payment_type, description) VALUES
(1, 'credit card'),
(2, 'cash'),
(3, 'no charge'),
(4, 'dispute'),
(5, 'unknown'),
(6, 'voided trip');

ALTER TABLE payment_types OWNER TO nyc_data_user;
GRANT ALL PRIVILEGES ON TABLE payment_types TO nyc_data_user;


CREATE TABLE IF NOT EXISTS "rates"(
    rate_code   INTEGER,
    description TEXT
);
INSERT INTO rates(rate_code, description) VALUES
(1, 'standard rate'),
(2, 'JFK'),
(3, 'Newark'),
(4, 'Nassau or Westchester'),
(5, 'negotiated fare'),
(6, 'group ride');

ALTER TABLE payment_types OWNER TO nyc_data_user;
GRANT ALL PRIVILEGES ON TABLE payment_types TO nyc_data_user;

EOSQL