Postgresql on Mac

postgresql_mac.md

Installing

Reference

brew update
brew install postgresql
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

Add alias to zshrc

subl ~/.zshrc # or vim ~/.zshrc

At the bottom of the file, create two new aliases to start and stop your postgres server.

alias pg-start="launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist"
alias pg-stop="launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist"

Run the command: source ~/.zshrc to reload your configuration.

• pg-start Use this comment to start your database service
• pg-stop stops your database service
• psql Connect to your postgres

Mini-tutorials

Reference

Create Database

createdb mydb # create mydb database
dropdb mydb # delete mydb database
createdb mydb # create mydb database again
psql mydb # access mydb database

Now you are in the mydb database enter SELECT current_date; and SELECT 2 + 2;

psql (10.2)
Type "help" for help.

mydb=# SELECT current_date;
 current_date
--------------
 2018-02-20
(1 row)

mydb=# SELECT 2 + 2;
 ?column?
----------
        4
(1 row)

To get out of psql, type:

mydb=> \q

Re-enter mydb

psql mydb

Creating a New Table

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

Remove Table

DROP TABLE tablename;

Populating a Table With Rows

In Database

  INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
  INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', 'Hayward', 54, 37);

Use Copy

COPY weather FROM '/home/user/weather.txt';

Querying a Table

SELECT * FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;
SELECT * FROM weather
    ORDER BY city;
SELECT * FROM weather
    ORDER BY city, temp_lo;
SELECT DISTINCT city
    FROM weather;

Joins Between Tables

Inner Join

SELECT *
    FROM weather, cities
    WHERE city = name;
SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

Self Join

  SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

Aggregate Functions

City with the highest temperature

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

Aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation)

Updates

You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

Deletions

DELETE FROM weather WHERE city = 'Hayward';

DELETE FROM tablename;

Without a qualification, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this!

Advanced Features

Views

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.

Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.

Foreign Keys

maintaining the referential integrity of your data

Transactions

The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation.

Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the current row.

• do not cause rows to become grouped into a single output
• the rows retain their separate identities

The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

Inheritance

Inheritance is a concept from object-oriented databases.