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.
Top comments (0)