PostgreSQL commands

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;