Tables
Links
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_name | last_name | gender | date_of_birth | |
---|---|---|---|---|
Anne | Smith | FEMALE | 09/01/88 | |
Jake | Jones | MALE | 09/01/90 | jake@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
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;