General Commands:
- Connection
psql -h localhost -d johto -U silver
Prompt: Password for user silver:
(Enter your password and press enter)
- List Databases
\l
Prompt:
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)
- List Tables
\dt
Prompt:
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | user | table | silver
(1 row)
User and Role Management:
- Create User
CREATE USER silver WITH PASSWORD 'totodileRulez';
Prompt: CREATE ROLE
- Grant Privileges
GRANT ALL PRIVILEGES ON DATABASE johto TO silver;
Prompt: GRANT
- Create Role
CREATE ROLE trainer WITH LOGIN PASSWORD 'totodileRulez';
Prompt: CREATE ROLE
Database Actions:
- Create Database
CREATE DATABASE johto;
Prompt: CREATE DATABASE
- Drop Database
DROP DATABASE johto;
Prompt: DROP DATABASE
Query Methods:
- Select All
SELECT * FROM pokemon;
Prompt:
id | name
----+----------
1 | Teddiursa
2 | Sunkern
(2 rows)
- Where Clause
SELECT * FROM pokemon WHERE name = 'Espeon';
Prompt:
id | name
----+----------
1 | Espeon
(1 row)
Complex Commands:
- Join Tables
SELECT * FROM pokemon INNER JOIN moves ON pokemon.id = moves.pokemon_id;
Prompt:
pokemon_id | name | move_id | move
------------+---------+---------+----------
1 | Teddiursa| 1 | Tackle
2 | Sunkern | 2 | Vine Whip
(2 rows)
More Query Methods:
- Max
SELECT MAX(level) FROM pokemon;
Prompt:
max
-----
100
(1 row)
- Min
SELECT MIN(level) FROM pokemon;
Prompt:
min
-----
1
(1 row)
- Avg
SELECT AVG(level) FROM pokemon;
Prompt:
avg
---------------------
50.5000000000000000
(1 row)
- Sum
SELECT SUM(level) FROM pokemon;
Prompt:
sum
-----
101
(1 row)
- Group By
SELECT COUNT(*), type FROM pokemon GROUP BY type;
Prompt:
count | type
-------+-------------
2 | Dark
3 | Steel
(2 rows)
- Order By
SELECT * FROM pokemon ORDER BY level DESC;
Prompt:
id | name | level | type
----+-----------+-----+---------
1 | Unown| 100 | Psychic
3 | Snubbul| 33 | Fairy
2 | Ursaring| 1 | Normal
(3 rows)
More Complex Commands:
- Subquery
SELECT * FROM (SELECT * FROM pokemon ORDER BY level DESC LIMIT 2) AS subquery;
Prompt:
id | name | level | type
----+-----------+-----+---------
1 | Chikorita | 100 | Grass
3 | Cyndaquil | 33 | Fire
(2 rows)
- Create Index
CREATE INDEX idx_pokemon_level ON pokemon(level);
Prompt: CREATE INDEX
- Create View
CREATE VIEW high_level_pokemon AS SELECT * FROM pokemon WHERE level > 50;
Prompt: CREATE VIEW
- Select from View
SELECT * FROM high_level_pokemon;
Prompt:
id | name | level | type
----+-----------+-----+---------
1 | Totodile | 100 | Water
(1 row)
More Query Methods:
- Select Distinct
SELECT DISTINCT type FROM pokemon;
Prompt:
type
-----------
Grass
Fire
(2 rows)
- Like Operator
SELECT * FROM pokemon WHERE name LIKE '%quil%';
Prompt:
id | name | level | type
----+-----------+-----+---------
1 | Cyndaquil | 100 | Fire
2 | Quilava | 1 | Fire
(2 rows)
Advanced Complex Commands:
- Right Join
SELECT * FROM moves RIGHT JOIN pokemon ON pokemon.id = moves.pokemon_id;
Prompt:
pokemon_id | name | move_id | move
------------+-------+---------+----------
1 | Chikorita| 1 | Tackle
2 | Bayleef | 2 | Vine Whip
3 | Cyndaquil | NULL | NULL
(3 rows)
- Full Outer Join
SELECT * FROM moves FULL OUTER JOIN pokemon ON pokemon.id = moves.pokemon_id;
Prompt:
pokemon_id | name | move_id | move
------------+-------+---------+----------
1 | Chikorita| 1 | Tackle
2 | Bayleef | 2 | Vine Whip
3 | Cyndaquil | NULL | NULL
(3 rows)
- Self Join
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)
Function and Procedures:
- Creating Functions
CREATE FUNCTION increase_level(integer, integer) RETURNS integer AS $$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;
Prompt: CREATE FUNCTION
- Calling Functions
SELECT increase_level(1, 2);
Prompt:
increase_level
----------------
3
(1 row)
- Creating Procedures
CREATE PROCEDURE archive_old_pokemon() AS $$
BEGIN
DELETE FROM pokemon WHERE level < 10;
END;
$$ LANGUAGE plpgsql;
Prompt: CREATE PROCEDURE
- Calling Procedures
CALL archive_old_pokemon();
Prompt: CALL
- Creating Triggers
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;
Prompt: CREATE TRIGGER
Error Handling:
PostgreSQL returns errors with specific SQLSTATE values. When an error occurs, an exception is thrown with an associated SQLSTATE value.
For example, if you attempt to create a table that already exists:
CREATE TABLE pokemon(id SERIAL PRIMARY KEY, name VARCHAR(100));
You might get an error like:
ERROR: relation "pokemon" already exists
In this case, you would need to either drop the existing table or change the name of the table you are trying to create.