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 ingredientsIt 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 thereEXPLAIN 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 isn't always 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. Postgres used a sequential scan in this case.
Let's set up for indexes, 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 sequential scan in this case. Frequently, the cost of setting up to use an index will exceed the cost of doing a sequential search.
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.
If you're creating indexes, make sure you're using them. If you're not using them, then drop them.
To see indexes, \d <database_name>. In this case, \d ingredients
DROP INDEX <index_name>With UNIQUE constraint here, this will create an index for you. So when you start searching for addresses by completing with street, city and state, you'll be using an index.
So that unique constraint can be useful for more than just keeping things unique in your tables. It can also be useful for lookups it also helps for insertion performance.
CREATE TABLE american_addresses (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
street_address int NOT NULL,
city VARCHAR(255) NOT NULL,
state VARCHAR(255) NOT NULL,
UNIQUE (street_address, city, state)
);DELETE FROM my_table WHERE my_id = 1;CREATE VIEW {view_name} AS SELECT * FROM "user";