-
Notifications
You must be signed in to change notification settings - Fork 0
PostgreSQL commands
Daisho Komiyama edited this page Dec 28, 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
);
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;
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 ingredients SET image = 'strawberry.jpg' WHERE title = 'watermelon';
UPDATE ingredients SET image = 'watermelon.jpg' WHERE title = 'watermelon'
RETURNING title, image, id, type;
or returning everything
RETURNING *
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;