Postgres Example

Author: Tom Wragg

Postgres Example

In the previous section we installed postgres using brew and verified that we can login to the default database.

We will now create an example to load data into a new database before moving on to somewhat more useful examples ;)

Open your terminal and type the following commands to create a new folder and database script:-

mkdir postgres

cd postgres

vi db_create.sql

image

Paste the following database creation script and save the file:-

DROP DATABASE IF EXISTS nakame;
CREATE DATABASE nakame;

DROP USER IF EXISTS nakame;
CREATE USER nakame WITH ENCRYPTED PASSWORD 'admin';
GRANT ALL PRIVILEGES ON DATABASE nakame TO nakame;

\c nakame;

DROP TABLE IF EXISTS public.stock_eod;

CREATE TABLE public.stock_eod
(
    Date date,
    Open double precision,
    High double precision,
    Low double precision,
    Close double precision,
    Adjusted_close double precision,
    Volume double precision
);

ALTER TABLE public.stock_eod OWNER to nakame;

image

Using the above script create a new database with new user, table and permissions:-

whoami

psql -U USERID -d postgres -f db_create.sql

Note - replace USERID with the ID from the output of the whoami command.

image

Check that the database and tables are created with no rows:-

psql -U nakame -d nakame

\dt

SELECT * FROM stock_eod;

\q

image

Download historical Apple stock EOD (end of day) data removing the header and checking total rows:-

wget "https://eodhistoricaldata.com/api/eod/AAPL.US?api_token=demo&period=d" -O AAPL.csv

sed -i '' -e '1d' AAPL.csv

head AAPL.csv

wc AAPL.csv

image

Note - you can use brew to install the wget command if not already installed.

brew install wget

 

Load the data into the newly created database table checking total rows and running a simple query:-

psql -U nakame -d nakame

\copy stock_eod from 'AAPL.csv' DELIMITER ',' CSV;

SELECT COUNT(*) FROM stock_eod;

SELECT * FROM stock_eod WHERE Date = '2007-06-29';

image

Bingo! You have just completed a postgres database load example.

 


postgres
database