syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me

Question:
How to compare PostgreSQL to MongoDB?

On Ubuntu it is easy to install both PostgreSQL and MongoDB on the same host.

Then it is easy to compare them.

PostgreSQL AKA Postgres

When I installed Postgres on my Linux laptop I followed some steps.

I issued a shell command:
apt-get install postgresql postgresql-server-dev-all libpq-dev
After that finished, I rebooted the laptop.

The next series of steps depend on the name of my Linux account.

On my laptop I login to Linux as dan.

Postgres works well on my laptop if I create an account inside of Postgres named dan.

Also Postgres wants me to create a database inside of Postgres named dan.

So I did those two tasks using some simple commands:
sudo su - postgres
The above command put me into the Linux-Postgres account.

Then I used a command name psql to connect to the Postgres instance.

I saw this:
postgres@nia111:~$
postgres@nia111:~$
dan@nia111:~/x611 $ sudo su - postgres
[sudo] password for dan:
postgres@nia111:~$
postgres@nia111:~$
postgres@nia111:~$
postgres@nia111:~$ psql
psql (9.3.11)
Type "help" for help.
postgres=#
postgres=#
The last prompt looks like a shell prompt but it is actually a SQL prompt.

I have seen many people mistake a Postgres SQL prompt for a Linux shell prompt.

Next I created a dan account inside of Postgres:
create role dan with superuser login;
Then, I created a database named dan:
create database dan;
Next, I entered '\q' which allowed me to exit psql.

Then, I entered the shell command: exit which allowed me to leave the postgres Linux account and return to my dan Linux account.

Copy, Create, List, Update, Delete (CCLUD)

When I encounter a new technology I learn it by understanding both nouns and verbs which frequently appear inside the technology.

Five verbs which I usually see in software systems are listed below:

Copy, Create, List, Update, Delete (CCLUD)

The above five verbs are prominent within Postgres.

Above I displayed two examples of the Create-verb:
  • I created the role, dan.
  • I created the database, dan.
Also I updated the role, dan by giving it two privileges: superuser and login.

Postgres Nouns

For Postgres, the nouns which I want to operate on with the 5 verbs are listed below:
  • Postgres-Instance
  • Postgres-Linux-Account
  • Postgres-Role
  • Postgres-Database
  • Postgres-Table
  • Postgres-Row
  • Postgres-Index
  • Postgres-View
The most common verb-noun combinations are listed below:
  • Create Table
  • List Table
  • Delete Table
  • Copy Rows
  • Create Rows
  • List Rows
  • Update Rows
  • Delete Rows
Examples of the 8 above combinations are listed below.

Postgres: Create Table

The main command line interface (CLI) for Postgres is psql. When I work with Postgres I often place my syntax in a script and then feed the script to psql. Here is a simple demo of me running a one line SQL script:
echo "select 'hello world';" > /tmp/myscript.sql
psql -f /tmp/myscript.sql
I created a script which is a demo of the idea Create Table:

--
-- cr_postgres_table.sql
--

-- This is a simple demo of create table in Postgres

-- Demo:
-- psql -f cr_postgres_table.sql

create table if not exists tkrdates
(tkr   varchar
,cdate date
,cp    numeric
)
;

-- An alternative syntax
drop   table tkrdates;
create table tkrdates
(tkr   varchar
,cdate date
,cp    numeric
)
;
So, I ran the above script on my laptop and asked, "How to list that table?"

Postgres: List Table

To list my tables I start psql and then issue a simple command. A demo is listed below:

dan@nia111:~/x611 $ psql
psql (9.3.11)
Type help for help.

dan=# \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | tkrdates   | table | dan
 public | ydata      | table | dan
 public | ydata_copy | table | dan
(3 rows)

dan=# dan=# \q
dan@nia111:~/x611 $ 
dan@nia111:~/x611 $ 

Postgres: Delete Table

In Postgres, the syntax I use to implement the Delete Table idea is 'drop table':
drop table mytable;

Postgres: Copy Rows

Postgres offers me a variety of ways to copy table rows from point a to point b. One useful technique is to copy the rows from a CSV file. Below I list some syntax to copy two CSV files from Yahoo into the /tmp/ folder.

#!/bin/bash

# wgetgspc.bash

# This script should get prices from yahoo.
cd /tmp

TKRH='%5EGSPC'
TKR='GSPC'
rm -f ${TKR}.csv

wget --output-document=${TKR}.csv http://ichart.finance.yahoo.com/table.csv?s=${TKRH}
echo 'tkr,cdate,cp'                                                      > ${TKR}2.csv
grep -v Date ${TKR}.csv|awk -F, -v tkr=$TKR '{print tkr "," $1 "," $5}' >> ${TKR}2.csv

TKR='SPY'
rm -f ${TKR}.csv

wget --output-document=${TKR}.csv http://ichart.finance.yahoo.com/table.csv?s=${TKR}
echo 'tkr,cdate,cp'                                                      > ${TKR}2.csv
grep -v Date ${TKR}.csv|awk -F, -v tkr=$TKR '{print tkr "," $1 "," $5}' >> ${TKR}2.csv

cat GSPC2.csv SPY2.csv|grep -v cdate > tkrdates.csv

exit
So, I ran the above script on my laptop and then ran a SQL script to copy tkrdates.csv into a table called tkrdates:

--
-- copycsv.sql
--

-- This script should copy CSV data into tkrdates table.
-- I should run wgetgspc.bash before I run this script so that I will have CSV data.

-- Demo:
-- psql -f copycsv.sql

drop   table tkrdates;
create table tkrdates
(tkr   varchar
,cdate date
,cp    numeric
)
;

COPY tkrdates
(
tkr
,cdate
,cp
) FROM '/tmp/tkrdates.csv' WITH csv
;
When I ran the above script it issued very little output:
dan@nia111:~/x611/public/apr16 $
dan@nia111:~/x611/public/apr16 $ psql -f copycsv.sql
DROP TABLE
CREATE TABLE
COPY 22525
dan@nia111:~/x611/public/apr16 $
dan@nia111:~/x611/public/apr16 $

Postgres: Create Rows

The above demo actually demonstrated two ideas: Copy Rows and Create Rows. Usually I use the postgres insert command to implement the Create Rows idea. Below, I list a demo of postgres insert:
insert into tkrdates(tkr,cdate,cp) values ('IBM','2016-04-15',151.72);
select tkr,cdate,cp from tkrdates where tkr = 'IBM';

Postgres: List Rows

In Postgres I use the 'select' command to implement the List Rows idea.

The 'select' command is by far, the most interesting and useful feature of Postgres.

I list some demos below:

--
-- select.sql
--

-- This script is a demo of some select statements.
-- Demo:
-- psql -a -P pager=no -f select.sql

select count(tkr)      from tkrdates;
select count(tkr)      from tkrdates where tkr = 'GSPC';
select tkr, count(tkr) from tkrdates group by tkr;

select tkr
,count(tkr)
,min(cdate)
,max(cdate)
from tkrdates group by tkr
;

select
a.cdate
,a.cp cp_spy
,b.cp cp_gspc
from tkrdates a, tkrdates b
where a.cdate = b.cdate
and   a.tkr   = 'SPY'
and   b.tkr   = 'GSPC'
and   a.cdate between '2015-12-01' and '2015-12-31'
order by a.cdate
;

select
count(cdate)
,avg(cp_spy)
,avg(cp_gspc)
,corr(cp_spy,cp_gspc)
from
(
select
a.cdate
,a.cp cp_spy
,b.cp cp_gspc
from tkrdates a, tkrdates b
where a.cdate = b.cdate
and   a.tkr   = 'SPY'
and   b.tkr   = 'GSPC'
and   a.cdate between '2015-12-01' and '2015-12-31'
order by a.cdate
) subq
;

DROP   TABLE window_demo;
CREATE TABLE window_demo AS
SELECT
cdate
,LAG(cp,1,cp)    OVER (ORDER BY cdate) lag_cp1d
,cp
,LEAD(cp,1,NULL) OVER (ORDER BY cdate) lead_cp
,AVG(cp) OVER (ORDER BY cdate ROWS BETWEEN 2 PRECEDING AND 0 PRECEDING) mvgavg
from tkrdates
where tkr = 'GSPC'
order by cdate
;

select * from window_demo where cdate > '2016-04-01';

-- to be continued.

to be continued...


syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me