-
Notifications
You must be signed in to change notification settings - Fork 0
PostgreSQL commands
Daisho Komiyama edited this page Dec 27, 2022
·
28 revisions
docker pull postgres:15
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
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
\l
\q
\?
\!
i.e., to print the list of contents \! ls
\d
CREATE DATABASE recipeguru;
\c recipeguru;
CREATE TABLE ingredients (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
title VARCHAR (255) UNIQUE NOT NULL
);
INSERT INTO ingredients (title) VALUES ('bell pepper');
SELECT * FROM ingredients;
You should see something like this,
id | title
----+-------------
1 | bell pepper
(1 row)
DROP TABLE ingredients;
Leave current database (i.e., connect to a database other than recipeguru) then,
DROP DATABASE recipeguru;
ALTER TABLE ingredients ADD COLUMN image VARCHAR (255)
\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)
ALTER TABLE ingredients DROP COLUMN image;
ALTER TABLE ingredients
ADD COLUMN image VARCHAR (255),
ADD COLUMN type VARCHAR (50) NOT NULL;
INSERT INTO ingredients (
title, image, type
) VALUES (
'red pepper', 'red_pepper.jpg', 'vegetable'
);
INSERT INTO ingredients (
"title", "image", "type" -- Notice the " here
) VALUES (
'red pepper', 'red_pepper.jpg', 'vegetable' -- and the ' here
);