Postgresql Cheat Sheet

psql -h localhost -d johto -U silver

\l

                              List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 johto     | myuser   | UTF8     | C.UTF-8 | C.UTF-8 |
 hoenn     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

\dt

          List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | user | table | silver
(1 row)

CREATE USER silver WITH PASSWORD 'totodileRulez';

GRANT ALL PRIVILEGES ON DATABASE johto TO silver;

CREATE ROLE trainer WITH LOGIN PASSWORD 'totodileRulez';

CREATE DATABASE johto;

DROP DATABASE johto;

SELECT * FROM pokemon;

 id |   name   
----+----------
  1 | Teddiursa
  2 | Sunkern
(2 rows)

SELECT * FROM pokemon WHERE name = 'Espeon';

 id |   name   
----+----------
  1 | Espeon
(1 row)

SELECT * FROM pokemon INNER JOIN moves ON pokemon.id = moves.pokemon_id;

 pokemon_id | name    | move_id | move  
------------+---------+---------+----------
       1 | Teddiursa|        1 | Tackle
       2 | Sunkern |        2 | Vine Whip
(2 rows)

SELECT MAX(level) FROM pokemon;

 max
-----
  100
(1 row)

SELECT MIN(level) FROM pokemon;

 min
-----
  1
(1 row)

SELECT AVG(level) FROM pokemon;

        avg
---------------------
 50.5000000000000000
(1 row)

SELECT SUM(level) FROM pokemon;

 sum
-----
 101
(1 row)

SELECT COUNT(*), type FROM pokemon GROUP BY type;

 count |   type   
-------+-------------
    2 | Dark
    3 | Steel
(2 rows)

SELECT * FROM pokemon ORDER BY level DESC;

 id |   name    | level | type 
----+-----------+-----+---------
  1 | Unown|  100 | Psychic
  3 | Snubbul|  33 | Fairy
  2 | Ursaring|  1 | Normal
(3 rows)

SELECT * FROM (SELECT * FROM pokemon ORDER BY level DESC LIMIT 2) AS subquery;

 id |   name    | level | type 
----+-----------+-----+---------
  1 | Chikorita |  100 | Grass
  3 | Cyndaquil |  33 | Fire
(2 rows)

CREATE INDEX idx_pokemon_level ON pokemon(level);

CREATE VIEW high_level_pokemon AS SELECT * FROM pokemon WHERE level > 50;

SELECT * FROM high_level_pokemon;

 id |   name    | level | type 
----+-----------+-----+---------
  1 | Totodile  |  100 | Water
(1 row)

SELECT DISTINCT type FROM pokemon;

  type  
-----------
 Grass
 Fire
(2 rows)

SELECT * FROM pokemon WHERE name LIKE '%quil%';

 id |   name    | level | type 
----+-----------+-----+---------
  1 | Cyndaquil |  100 | Fire
  2 | Quilava |  1 | Fire
(2 rows)

SELECT * FROM moves RIGHT JOIN pokemon ON pokemon.id = moves.pokemon_id;

 pokemon_id | name  | move_id | move  
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)

SELECT * FROM moves FULL OUTER JOIN pokemon ON pokemon.id = moves.pokemon_id;

 pokemon_id | name  | move_id | move  
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)

SELECT A.name, B.name FROM pokemon A, pokemon B WHERE A.type = B.type AND A.name != B.name;

   name    |   name   
-----------+----------
 Chikorita| Bayleef 
 Bayleef | Chikorita
(2 rows)

CREATE FUNCTION increase_level(integer, integer) RETURNS integer AS $$
BEGIN
   RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

SELECT increase_level(1, 2);

 increase_level 
----------------
              3
(1 row)

CREATE PROCEDURE archive_old_pokemon() AS $$
BEGIN
   DELETE FROM pokemon WHERE level < 10;
END;
$$ LANGUAGE plpgsql;

CALL archive_old_pokemon();

CREATE TRIGGER check_level_before_insert 
BEFORE INSERT ON pokemon 
FOR EACH ROW 
WHEN (NEW.level > 100) 
DO 
$$ 
BEGIN 
   RAISE EXCEPTION 'Level cannot be more than 100'; 
END; 
$$ LANGUAGE plpgsql;

CREATE TABLE pokemon(id SERIAL PRIMARY KEY, name VARCHAR(100));

ERROR:  relation "pokemon" already exists