PostgreSQL - data manipulation

Insert

-- you don't provide value for SERIAL field
INSERT INTO account(username, password, email, created_on)
VALUES ('admin', 'password1', 'admin@gmal.com', CURRENT_TIMESTAMP);

INSERT INTO job(job_name, job_description)
VALUES ('Software Engineer', 'Good understanding of SQL, AWS');

-- you can insert multiple values
INSERT INTO job(job_name, job_description)
VALUES ('President', 'Loyal to the country.'),
       ('Cleaner', 'Hard working');

INSERT INTO account_job(user_id, job_id, hire_date)
VALUES (1, 1, CURRENT_TIMESTAMP);

-- you need to match all the constraints (NOT NULL, FOREIGN KEY etc.)
-- [23503] ERROR: insert or update on table "account_job" violates foreign key constraint
-- "account_job_user_id_fkey" Detail: Key (user_id)=(10) is not present in table "account".
INSERT INTO account_job(user_id, job_id, hire_date)
VALUES (10, 10, CURRENT_TIMESTAMP);

Delete

-- always to WHERE, otherwise you'll delete all rows by mistake
-- use TRUNCATE to remove all rows which is faster
delete from job
where job_name = 'TypeScript';

-- return the rows removed
delete from job
where job_name = 'Scala'
returning *;

-- There are two ways to delete rows in a table using information contained in other tables in the database:
-- using sub-selects, or specifying additional tables in the USING clause.

-- 1. delete rows using information contained
delete from job
where job_id not in (
select job.job_id
from job inner join account_job on job.job_id = account_job.job_id
)
returning *;

-- 2. delete join: use USING keyword (the query is just an example, may not work due to foreign key constraint)
delete from job
using account_job
where job.job_id != account_job.job_id;

Update

-- UPDATE...SET...WHERE, please use WHERE otherwise the change will be applied to all rows
update account
set last_login = CURRENT_TIMESTAMP
where last_login is null;

-- update to the value of another column
update account
set last_login = created_on
where user_id = 1;

-- update join: update to the value of the column from another table using an implicit JOIN
update account_job
set hire_date = account.created_on
from account
where account_job.user_id = account.user_id;

-- update join with AS: use AS for table alias, just as in SELECT statements
update account_job as aj
set hire_date = a.created_on
from account as a
where aj.user_id = a.user_id;

-- UPDATE...RETURN: return column values after the update
update account
set last_login = CURRENT_TIMESTAMP
where last_login < now()
returning user_id, last_login, email;