SQL for Postgres

A major part of my programming work consists of searching for things online. This can be to debug errors, find solutions to problems, look up documentation or follow tutorials on how to achieve a specific thing.

Often I end up looking for the same thing many times because I keep forgetting how to do it. So I got the idea of writing down here the things I look up more than once. For now, I will call them snippets.

The goal is to 1) remember better what I look up by writing it up and explaining it, and 2) save me time the next time I need to find something I have used more than once before. If these snippets can save time for others, that would be a great bonus.

The first thing I am writing down is the SQL I look up the most when writing queries for Postgres.

Select (docs)

SELECT e.*, c.name
FROM employees e
INNER JOIN company c on c.id = e.c_id
WHERE c.name = 'test'

INNER JOIN and JOIN are the same. To retain nulls, use LEFT JOIN.

Insert (docs)

INSERT INTO films
(code, title, did, date_prod, kind)
VALUES
('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Update (docs)

UPDATE employees
SET age = 34
WHERE id = 1
RETURNING name, gender;

Delete (docs)

DELETE FROM employees
WHERE id = 25
RETURNING *;

Alter sequence (docs)

ALTER SEQUENCE serial
RESTART WITH 105;

Use one number higher than the highest value in the sequence.