Skip to main content

Tables

Data Types | https://www.postgresql.org/docs/current/datatype.html

List Tables

\d

\d table_name

Create Table

-- Column name + data type + optional constraints
CREATE TABLE person (
id uuid NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50),
gender VARCHAR(6) NOT NULL,
date_of_birth DATE NOT NULL
);

Delete Table

DROP TABLE person;

Insert Data

Using Person Table insert

first_namelast_namegenderdate_of_birthemail
AnneSmithFEMALE09/01/88
JakeJonesMALE09/01/90jake@email.com

Anne

INSERT INTO person (
id,
first_name,
last_name,
gender,
date_of_birth
)
VALUES (
gen_random_uuid(),
'Anne',
'Smith',
'FEMALE',
DATE '1988-01-09' --YYYY-MM-DD
);

Jake

INSERT INTO person (
id,
first_name,
last_name,
gender,
date_of_birth,
email
)
VALUES (
gen_random_uuid(),
'Jake',
'Jones',
'MALE',
DATE '1990-01-09',
'jake@email.com'
);

Query Data From Table

select all columns

SELECT * FROM person;
db=# SELECT * FROM person;
id | first_name | last_name | email | gender | date_of_birth
--------------------------------------+------------+-----------+----------------+--------+---------------
57ed43a6-9919-4a22-9385-5c0bdd4507a4 | Anne | Smith | | FEMALE | 1988-01-09
107bb6d3-71d2-431a-9a8c-5e09b412d639 | Jake | Jones | jake@email.com | MALE | 1990-01-09
(2 rows)

select columns

SELECT first_name, last_name, gender FROM person;

OUT

db=# SELECT first_name, last_name, gender FROM person;
first_name | last_name | gender
------------+-----------+--------
Anne | Smith | FEMALE
Jake | Jones | MALE

Mockaroo

https://mockaroo.com/

use mockaroo to generate random seed data for demo purposes

DROP TABLE person;

Use psql to run script

psql -h localhost -U username -p 5432 -d db -f ./seed_person.sql 

Sorting

Order By

SELECT * FROM person ORDER BY country_of_birth DESC;
SELECT * FROM person ORDER BY email ASC;

Duplicates

SELECT country_of_birth FROM person ORDER BY country_of_birth;

Unique Countries

see each country only once

SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;

WHERE Clause

Filter data based on conditions

Equality operators

SELECT * FROM person WHERE gender = 'Female';
SELECT * FROM person WHERE gender = 'Male';
SELECT * FROM person WHERE gender = 'Female' AND country_of_birth = 'Poland';
SELECT * FROM person WHERE gender = 'Female' AND (country_of_birth = 'Poland' OR country_of_birth = 'China');

Comparison Operators

SELECT 1 = 1;
SELECT 1 = 2;
SELECT 1 < 2;
SELECT 2 <= 2;

OUT

db=# SELECT 1 = 1;
SELECT 1 = 2;
SELECT 1 < 2;
SELECT 2 <= 2;
?column?
----------
t
(1 row)

?column?
----------
f
(1 row)

?column?
----------
t
(1 row)

?column?
----------
t
(1 row)

SELECT 1 <> 3; -- not equal
SELECT * FROM person WHERE country_of_birth <> 'Poland' LIMIT 10;