PostgreSQL basics

[genkio]

Working with database

-- create database
create database test;

-- list all databases
\l

-- select database
\c test
-- test=#

-- remove database
drop database test;

\! cls

-- exit
\q

Working with table

-- create table
create table company(id int primary key not null, name text not null, age int not null, address char(50), salary real);

-- describe the table schema
\d

create table department(id int primary key not null, dept char(50) not null, emp_id not null);

-- remove table
drop table students;

INSERT

-- insert single value
insert into company(id, name, age, address, salary) values(7, 'Joe', 30, 'Mars', 1);

-- insert multiple values
insert into company(id, name, age, address, salary) values(7, 'Joe', 30, 'Mars', 1), (8, 'Doe', 20, 'Earth', 1)

SELECT

-- select all
select * from company;

-- select only certain columns
select id, name from company;

Arithmetic operators

select 2 + 3;

select 10 / 5;

select 14 % 4;

select 2 ^ 3;

WHERE clause

select name from company where age != 20;

select * from company where age > 20;

Logical operators

select * from company where age > 20 and salary > 1;

select * from company where age > 20 or salary > 1;

select * from company where salary is null;

-- start with J
select * from company where name like 'J%';
-- end with e
select * from company where name like '%e';
-- if there's a
select * from company where name like '%a%';

select * from company where age in('20', '30', '40');

Expressions

select name as emp_name from company;

select count(name) from company;

select max(salary) from company;

select min(salary) from company;

select sum(salary) from company;

select avg(salary) from company;

select current_timestamp;

CHECK constraint

create table products(
  product_no integer,
  name text,
  price numeric check(price > 0)
);

-- shall receive error when inserting
insert into products value(10, 'shoe', -1);

create table products(
  product_no integer,
  name text,
  price numeric check(price > 0),
  discount_price numeric check(discount_price > 0), check(price > discount_price)
);

UNIQUE and NOT NULL constraint

create table products(
  price numeric not null check(price > 0)
);

create table products(
  product_no integer unique,
);

create table products(
  product_no integer,
  name char(50),
  unique(product_no, name)
);

PRIMARY KEY and FOREIGN KEY

create table products(
  product_no integer primary key,
  name char(50),
  price numeric
);

create table products(
  product_no integer,
  name char(50),
  price numeric,
  primary key(product_no, name)
);

create table orders(
  order_id integer primary key,
  product_no integer references products(product_no),
  quantity integer not null check(quantity > 0)
);

create table order_items(
  product_no integer references products(product_no),
  order_id integer references orders(order_id),
  quantity integer not null check(quantity > 0),
  primary key(product_no, order_id)
);

-- alternative syntax
create table order_items(
  product_no integer,
  order_id integer,
  quantity integer not null check(quantity > 0),
  primary key(product_no, order_id),
  foreign key(product_no) references products(product_no),
  foreign key(order_id) references orders(order_id),
);

ON DELETE RESTRICT and DELETE CASCADE

create table order_items(
  -- on delete restrict prevent product (with this product_no) to be deleted before order_item deleted first
  product_no integer references products(product_no) on delete restrict,
  -- on delete cascade ensures when order (with this order_id) gets canceled (deleted), all associated order_items will also be deleted
  order_id integer references orders(order_id) on delete cascade,
  quantity integer not null check(quantity > 0),
  primary key(product_no, order_id)
);

ALTER table

alter table company add gender char(1);
alter table company drop column gender;

-- add primary key after table created
alter table company add constraint pri primary key(id);

alter table company drop constraint pri;

UPDATE and DELETE

update company set age = 30 where id = 1;

delete from company where id = 8;

LIMIT and OFFSET operators

select * from company limit 4;

-- show the next 4 records
select * from company limit 4 offset 4;

GROUP BY and HAVING clause

-- group records with the same values of certain column
select age, count(*) from company group by age;

-- having clause can't live alone without group by
select age, count(*) from company group by age having max(salary) > 10;

ORDER BY clause

select * from company order by salary desc;

-- sort by age asc and salary desc
select * from company order by age, salary desc;

select * from company order by age desc, salary desc;

Enumeration data type

create type mood as ENUM('sad', 'happy');

create table people(
  name text,
  current_mood mood
);

insert into people values('Joe', 'happy');

select max(current_mood) from people;

JSON data type

create table profiles(
  id integer,
  profile JSON
);

insert into profiles values(1, { 'name': 'Joe', 'address': { 'zipcode': 1 } });

select profile->>'name' from profiles;
select profile->'address'->>'zipcode' from profiles;
select profile#>>'{address, zipcode}' from profiles;

Array

create table employees(
  name text,
  pay integer[],
  schedule text[][]
);

insert into employees values('Joe', '{1, 2, 3, 4}', '{{"meeting", "lunch"}, {"training"}}');

select pay[2] from employees;
select name from employees where pay[1]<>pay[2];

update employees set pay='{5, 6, 7, 8}' where name = 'Joe';
update employees set pay[1]=0 where name = 'Joe';
-- partial update
update employees set pay[1:2] = '{9, 10}' where name = 'Joe';
-- append single value to all records
update employees set pay = array_append(pay, 11);
-- append value to all records
update employees set pay = array_cat(pay, ARRAY[12, 13]);

update employees set pay = array_remove(pay, 10);

-- search
select * from employees where pay[1] = 5 or pay[2] = 6;
-- Array.includes
select * from employees where 5 = ANY(pay);
-- Array.every
select * from employees where 5 = ALL(pay);

String functions

-- string concatenation
select 'Post'||'greSQL';
select char_length('hello');
select length('hello');
select lower('HELLO');
select upper('hello');
select substr('Hello', 2, 4); -- ello
select position('th' in 'thanks'); -- 1
select ascii('x'); -- 120
select concat('abc', 'edf');
select initcap('hello world'); -- Hello World
select repeat('hello', 2);
select reverse('hello');

Aggregate functions

-- display values as array
select array_agg(name) from company;

select json_agg(name) from company;

-- display values as key value pair
select json_object_agg(name, age) from company;

-- standard devision of numeric data
select stddiv(age) from company;

-- variance of numeric data
select variance(age) from company;

LIKE

-- find the name start with 'J'
select * from company where name like 'J%';

-- find the name end with 'e'
select * from company where name like '%e';

-- _ is the position placeholder, and % is the wild card
select * from company where name like '_oe%';

-- find the name with at least one 'o' regardless its position
select * from company where name like '%o%';

-- find the name start with 'J' and at least 3 chars in length
select * from company where name like 'J_%_%';

-- find the name with 5 chars in length, start with 'J' and end with 'e'
select * from company where name like 'J___e';

JOIN

-- cross join
-- no matching condition in the join clause, number of total rows after join is n (rows in company table) x m (rows in department table)
select emp_id, name, dept from company cross join department;

-- inner join
-- join common rows between tables base on match condition
select name, age, dept from company inner join department on company.id = department.id;

-- with alias
select name, age, dept from company c, department d where c.id = d.id;

-- left outer join, aka left join
-- table rows at the left side of JOIN will be fully included, joins the rows from the table at the right side of JOIN, base on the match condition
select name, age, dept from company left outer join department on company.id = department.id;

-- right outer join, aka right join
-- pretty much same as left outer join, what changes is which table will be fully included.
-- table rows at the right side of JOIN will be fully included, joins the rows from the table at the left side of JOIN, base on the match condition
select name, age, dept from company right outer join department on company.id = department.id;

-- full outer join
-- include table rows of all tables
select name, age, dept from company full outer join department on company.id = department.id;

Views

-- to expose only some of the columns with view
create view company_view as select id, name, age from company;

UNION and UNION ALL

-- combines the results of two select statement (with same columns selected)
-- UNION shows only the unique results, vs. UNION ALL shows all results
select company.id, name, department from company inner join department on company.id = department.id
union
select company.id, name, department from company left outer join department on company.id = department.id;

Truncate table

-- remove all data but keep table structure
truncate table company;

-- remove all data of this table, and also all data of tables which has foreign key to this table
truncate table company cascade;

Subqueries

-- together with IN
select * from company where age in (select age from company where salary > 100);

update company set salary = salary * 1.50 where age in (select age from company where age >= 30);

select * from company where age > (select avg(age) from company);

Index

explain select * from dummy;

create index name_index on dummy(first_name);

[dhassault]

Useful! Thanks :)