psql- start postgres terminal (logs into default database)psql <dbname>- start postgres terminal in specific database\l- list all databases\q- exit the terminal\d- list all tables in selected database- Make sure to end all commands with a
;character - You don't have to type commands in CAPSLOCK, lower case is fine
- You cannot delete a database that you are currently viewing (\c out of it first)
- Can use
--for comments
- Tables are grouped into databases
- Databases are grouped into clusters
CREATE DATABASE <database>;\c <database name>SELECT current_user;SELECT current_database();DROP DATABASE <database>;- Use the command
CREATE TABLE <tablename:
CREATE TABLE employees (
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
RANK INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 25500.00,
BDAY DATE DEFAULT '1900-01-01'
);\d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres\d <table name>
Table "public.employees"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+--------------------
id | integer | | not null |
name | text | | not null |
rank | integer | | not null |
address | character(50) | | |
salary | real | | | 25500.00
bday | date | | | '1900-01-01'::date
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)DROP TABLE <table name>;INSERT INTO employees (ID,NAME,RANK,ADDRESS,SALARY,BDAY) VALUES (1, 'Mark', 7, '1212 E. Lane, Someville, AK, 57483', 43000.00 ,'1992-01-13');SELECT * FROM <table name>;
id | name | rank | address | salary | bday
----+------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
(1 row)
- Values that are not included will be added using default values:
INSERT INTO employees (ID,NAME,RANK,ADDRESS,BDAY) VALUES (2, 'Marian', 8, '7214 Wonderlust Ave, Lost Lake, KS, 22897', '1989-11-21');
Table "public.employees"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+--------------------
salary | real | | | 25500.00
id | name | rank | address | salary | bday
----+--------+------+----------------------------------------------------+--------+------------
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21- Inserting multiple rows at once:
INSERT INTO employees (ID,NAME,RANK,ADDRESS,SALARY,BDAY) VALUES (4, 'Jasmine', 5, '983 Star Ave., Brooklyn, NY, 00912 ', 55700.00, '1997-12-13' ), (5, 'Orranda', 9, '745 Hammer Lane, Hammerfield, Texas, 75839', 65350.00 , '1992-12-13');- Done using either smallserial, serial or bigserial
CREATE TABLE phonenumbers(
ID SERIAL PRIMARY KEY,
PHONE TEXT NOT NULL
);
INSERT INTO phonenumbers (PHONE) VALUES ( '234-432-5234'), ('543-534-6543'), ('312-123-5432');
\d phonenumbers;
SELECT * FROM phonenumbers;- Creates a Cartesian product of the two tables
- Not very useful as it's just making every combination of records between the two tabes
create table person (
id serial primary key not null,
name char(50) not null,
age int not null,
gender char(2) default 'NA'
);insert into person (name, age, gender) values ('Bob', 24, 'M'), ('Jane', 34, 'F'), ('Jim', 32, 'M'), ('Li', 41, 'F'); id | name | age | gender
----+----------------------------------------------------+-----+--------
1 | Bob | 24 | M
2 | Jane | 34 | F
3 | Jim | 32 | M
4 | Li | 41 | F
create table sport (
id serial primary key not null,
name char(50) not null,
pid int references person(id)
);insert into sport (name, pid) values ('Surf', 1), ('Skate', 3), ('Run', 3), ('Bike', 2), ('Row', 1);----+----------------------------------------------------+-----
1 | Surf | 1
2 | Skate | 3
3 | Run | 3
4 | Bike | 2
5 | Row | 1
SELECT * FROM person CROSS JOIN sport;
id | name | age | gender | id | name | pid
----+----------------------------------------------------+-----+--------+----+----------------------------------------------------+-----
1 | Bob | 24 | M | 1 | Surf | 1
2 | Jane | 34 | F | 1 | Surf | 1
3 | Jim | 32 | M | 1 | Surf | 1
4 | Li | 41 | F | 1 | Surf | 1
1 | Bob | 24 | M | 2 | Skate | 3
2 | Jane | 34 | F | 2 | Skate | 3
3 | Jim | 32 | M | 2 | Skate | 3
4 | Li | 41 | F | 2 | Skate | 3
1 | Bob | 24 | M | 3 | Run | 3
2 | Jane | 34 | F | 3 | Run | 3
3 | Jim | 32 | M | 3 | Run | 3
4 | Li | 41 | F | 3 | Run | 3
1 | Bob | 24 | M | 4 | Bike | 2
2 | Jane | 34 | F | 4 | Bike | 2
3 | Jim | 32 | M | 4 | Bike | 2
4 | Li | 41 | F | 4 | Bike | 2
1 | Bob | 24 | M | 5 | Row | 1
2 | Jane | 34 | F | 5 | Row | 1
3 | Jim | 32 | M | 5 | Row | 1
4 | Li | 41 | F | 5 | Row | 1
- Allows you to join two tables based on the primary key and a foreign key in the same table
- returns the set of records where the two are equal
select * from person inner join sport on person.id = sport.pid;
id | name | age | gender | id | name | pid
----+----------------------------------------------------+-----+--------+----+----------------------------------------------------+-----
1 | Bob | 24 | M | 1 | Surf | 1
3 | Jim | 32 | M | 2 | Skate | 3
3 | Jim | 32 | M | 3 | Run | 3
2 | Jane | 34 | F | 4 | Bike | 2
1 | Bob | 24 | M | 5 | Row | 1
SELECT person.NAME, sport.NAME FROM person INNER JOIN sport ON person.id = sport.p_id;
name | name
----------------------------------------------------+----------------------------------------------------
Bob | Surf
Jim | Skate
Jim | Run
Jane | Bike
Bob | Row
- You can actually chain
inner joincommands to join more than one table based on primary key and foreign keys.
select customers.cid, customers.cfirst, movies.mid, movies.mmovie from customers inner join rentals on customers.cid=rentals.cid inner join movies on movies.mid=rentals.mid;- Outer joins will give you all of the items in tables, even if they aren't related to an item in the other.
- For instance if you have a table of sports, and not all sports are tied to a specific person, the outer join could show you either all of the people, regardless if they're related to a sport, or all of the sports, regardless if they're related to a person.
- There are three types of
outer joinqueries that can be done:left outer joinright outer joinfull outer join
- For the first two, what is returned depends on the order of the tables being joined in the command.
- The syntax for the outer join is
select <filter> from <table1> <outer join type> <table2> on <p.key>=<f.key>; - In the
leftorrightcommands will be based on whatever tables are present in the<table1>and<table2>fields above. Here are the two tables I'll be working with:
select * from person;
id | name | age | gender
----+----------------------------------------------------+-----+--------
1 | Bob | 24 | M
2 | Jane | 34 | F
3 | Jim | 32 | M
4 | Li | 41 | F select * from sport;
id | name | pid
----+----------------------------------------------------+-----
1 | Surf | 1
2 | Skate | 3
3 | Run | 3
4 | Bike | 2
5 | Row | 1- Based on the positioning of tables in the query like mentioned before.
- Will use the first table as the table from which to pull everything and join with table2 items.
- To get all of the people, and sports they're related to:
select person.name, sport.name from person left outer join sport on person.id=sport.pid;
name | name
----------------------------------------------------+----------------------------------------------------
Bob | Surf
Jim | Skate
Jim | Run
Jane | Bike
Bob | Row
Li | - Or to get all of the sports and the people they're related to:
select person.name, sport.name from sport left outer join person on sport.pid=person.id;
name | name
----------------------------------------------------+----------------------------------------------------
Bob | Surf
Jim | Skate
Jim | Run
Jane | Bike
Bob | Row
| Bowling - Basically the reverse of the left outer join, this uses the second table in the query to be queried from.
- So we can repeat the same thing that was done in the first example above by flipping the query around:
select person.name, sport.name from sport right outer join person on sport.pid=person.id;- Or the second:
select person.name,sport.name from person right outer join sport on person.id=sport.id;- The full outer join will grab all items from both lists, inclduing both those that match and those that don't
select person.*, sport.name from sport full outer join person on sport.pid=person.id;
id | name | age | gender | name
----+----------------------------------------------------+-----+--------+----------------------------------------------------
1 | Bob | 24 | M | Surf
3 | Jim | 32 | M | Skate
3 | Jim | 32 | M | Run
2 | Jane | 34 | F | Bike
1 | Bob | 24 | M | Row
| | | | Bowling
4 | Li | 41 | F |
- Used to help pair down and bettery query for data
- Clauses:
whereandorinnotbetweenis not nulllikelimitorder by
Starting with the following table:
select * from employees;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
4 | Jasmine | 5 | 983 Star Ave., Brooklyn, NY, 00912 | 55700 | 1997-12-13
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where salary > 60000;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where salary > 60000 and rank > 7;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13select * from employees where salary > 60000 or rank > 7;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where rank in (1,4,6,8);
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where rank not in (1,4,6,8);
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
4 | Jasmine | 5 | 983 Star Ave., Brooklyn, NY, 00912 | 55700 | 1997-12-13
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13select * from employees where rank between 3 and 7 or salary between 40000 and 80000;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
4 | Jasmine | 5 | 983 Star Ave., Brooklyn, NY, 00912 | 55700 | 1997-12-13
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where bday is not null;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
4 | Jasmine | 5 | 983 Star Ave., Brooklyn, NY, 00912 | 55700 | 1997-12-13
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees where name like '%Ma%';
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01select * from employees limit 1;
id | name | rank | address | salary | bday
----+------+------+----------------------------------------------------+--------+------------
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13select * from employees order by rank;
id | name | rank | address | salary | bday
----+---------+------+----------------------------------------------------+--------+------------
4 | Jasmine | 5 | 983 Star Ave., Brooklyn, NY, 00912 | 55700 | 1997-12-13
3 | Maxwell | 6 | 7215 Jasmine Place, Corinda, CA 98743 | 87500 | 1900-01-01
1 | Mark | 7 | 1212 E. Lane, Someville, AK, 57483 | 43000 | 1992-01-13
2 | Marian | 8 | 7214 Wonderlust Ave, Lost Lake, KS, 22897 | 25500 | 1989-11-21
5 | Orranda | 9 | 745 Hammer Lane, Hammerfield, Texas, 75839 | 65350 | 1992-12-13- To update a record, you need to make sure that you use a where clause to specify an exact record, otherwise EVERY record is going to be updated.
- Update syntax:
update <table> set <col1> = <val1> where <condition>;
update employees set salary = 26000 where id=2;- Just like update, need to ensure that we use the where clause to specify exact record, or everything will be deleted!
- Delete syntax:
delete from <table> where <condition>;
delete from sports where id=6;\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
create user bob with password 'password';
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
bob | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}privileges: SELECT, INSERT, UPDATE, DELETE, RULE, ALL
grant all privileges on database company to bob;
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+------------+------------+-----------------------
blockbuster | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
company | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | bob=CTc/postgres- grant privileges on different databases.
revoke all privileges on database company from bob;- changes a users roles.
alter user bob with superuser;
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
bob | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}drop user bob;