-
Notifications
You must be signed in to change notification settings - Fork 0
PostgreSQL commands
reference: https://sql.holt.courses/
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;
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)
Print current time
SELECT NOW();
Print lowercased string
SELECT LOWER('HI THERE'); # hi there
EXPLAIN ANALYZE SELECT * FROM ingredients WHERE image='blueberry.jpg';
-- output
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on ingredients (cost=0.00..10.75 rows=1 width=1154) (actual time=0.059..0.066 rows=1 loops=1)
Filter: ((image)::text = 'blueberry.jpg'::text)
Rows Removed by Filter: 30
Planning Time: 0.317 ms
Execution Time: 0.217 ms
(5 rows)
Just so you know, index search will never always be the fastest option, as indexing comes with a cost. For the above example, planning time takes 0.317 ms and execution time takes 0.217 ms. Thus it takes 0.534 ms in total. That search is not indexed; it's a scan.
Let's index the image table, then run a search.
CREATE INDEX idx_name ON ingredients(image);
EXPLAIN ANALYZE SELECT * FROM ingredients WHERE image='blueberry.jpg';
-- output
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on ingredients (cost=0.00..1.39 rows=1 width=1154) (actual time=0.041..0.049 rows=1 loops=1)
Filter: ((image)::text = 'blueberry.jpg'::text)
Rows Removed by Filter: 30
Planning Time: 1.388 ms
Execution Time: 0.104 ms
(5 rows)
After indexing the table, execution time was reduced to 0.104 ms (-113ms), but planning time took 1.388 ms (+1.071 ms), 1.492 ms in total. Index search is three times slower than a scan in this case.
Note that a few thousand rows are not that much. Postgres can deal with billions of rows. That's why searching without index is still fast.
To see indexes, \d <database_name>
. In this case, \d ingredients
DROP INDEX <index_name>