PostgreSQL commands

reference: https://sql.holt.courses/

Starting with a docker

docker pull postgres:15

Run PostgreSQL container

docker run -e POSTGRES_PASSWORD=<your_password> --name=<your_container_name> --rm -d -p 5432:5432 postgres:15

-e = environment variable (i.e., POSTGRES_PASSWORD)
-d = detaches (runs in the background)
-p = exposes to port

Connect to PostgreSQL sessiondo

docker exec -u postgres -it <your_container_name> psql

-u = user
psql = a name of the command that we're running inside of the container, which is the Postgres client, psql is the client to run queries against the database

List databases

\l

Exit from the session

\q

Help

\?

Run bash command

\!

i.e., to print the list of contents \! ls

Print tables, sequences and constraints

\d

Create a database called recipeguru

CREATE DATABASE recipeguru;

Connect to a database

\c recipeguru;

Create a table

CREATE TABLE ingredients (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR (255) UNIQUE NOT NULL
);

Insert a row

INSERT INTO ingredients (title) VALUES ('bell pepper');

Print all from ingredients table

SELECT * FROM ingredients;

You should see something like this,

 id |    title
----+-------------
  1 | bell pepper
(1 row)

Drop a table

DROP TABLE ingredients;

Drop a database

Leave current database (i.e., connect to a database other than recipeguru) then,

DROP DATABASE recipeguru;

Alter a table

ALTER TABLE ingredients ADD COLUMN image VARCHAR (255)

Describe ingredients table

\d ingredients

It should prints,

                              Table "public.ingredients"
 Column |          Type          | Collation | Nullable |           Default
--------+------------------------+-----------+----------+------------------------------
 id     | integer                |           | not null | generated always as identity
 title  | character varying(255) |           | not null |
 image  | character varying(255) |           |          |
Indexes:
    "ingredients_pkey" PRIMARY KEY, btree (id)
    "ingredients_title_key" UNIQUE CONSTRAINT, btree (title)

Drop a column

ALTER TABLE ingredients DROP COLUMN image;

ALTER multiple table at once

ALTER TABLE ingredients
ADD COLUMN image VARCHAR (255),
ADD COLUMN type VARCHAR (50) NOT NULL;

INSERT data

INSERT INTO ingredients (
title, image, type
) VALUES (
'red pepper', 'red_pepper.jpg', 'vegetable'
);

How to leave comments (use --)

INSERT INTO ingredients (
"title", "image", "type" -- Notice the " here 
) VALUES (
'red pepper', 'red_pepper.jpg', 'vegetable' -- and the ' here 
);

Insert multiple data EXCEPT on conflict

ON CONFLICT DO NOTHING: if you hit any of these rows that you want to let me insert, just drop it, and keep going.

INSERT INTO ingredients (
  title, image, type
) VALUES
  ( 'avocado', 'avocado.jpg', 'fruit' ),
  ( 'banana', 'banana.jpg', 'fruit' )
ON CONFLICT DO NOTHING;

Update on conflict instead of doing nothing

INSERT INTO ingredients (
title, image, type
) VALUES (
'watermelon', 'banana.jpg', 'this won''t be updated man'
) ON CONFLICT (title) DO UPDATE SET image = 'default.jpg';

Update

UPDATE ingredients SET image = 'strawberry.jpg' WHERE title = 'watermelon';

Specify return value

UPDATE ingredients SET image = 'watermelon.jpg' WHERE title = 'watermelon'
RETURNING title, image, id, type;

or returning everything

RETURNING *

Select specific stuff

SELECT title, type FROM ingredients LIMIT 10;

I want all columns from the 11th item to the 20th item.

SELECT * FROM ingredients LIMIT 10 OFFSET 10;

SELECT * FROM ingredients WHERE id > 23 LIMIT 10;

Select all where type is NOT fruit.

SELECT * FROM ingredients WHERE type<>'fruit';

Select all from id 10 to 20.

SELECT * FROM ingredients WHERE id >= 10 AND id <= 20 LIMIT 10;

Order by id in descending order

SELECT * FROM ingredients ORDER BY id DESC LIMIT 10;

Search

SELECT * FROM ingredients WHERE title LIKE 'pota%';

Case insensitive search

SELECT * FROM ingredients WHERE title ILIKE 'PoTA%';

SELECT * FROM ingredients WHERE CONCAT(title, type) ILIKE '%fruit%';

Note: search string is just a string, so for example, %rryfru% matches blueberry (title) of type fruit.

SELECT * FROM ingredients WHERE title ILIKE 'ch_rry';

 id | title  |   image    | type
----+--------+------------+-------
 12 | cherry | cherry.jpg | fruit
(1 row)

Miscellaneous

Print current time

SELECT NOW();

Print lowercased string

SELECT LOWER('HI THERE'); # hi there