Skip to content

Instantly share code, notes, and snippets.

@K3ndev

K3ndev/postgresql.md

Last active July 15, 2024 00:21
Show Gist options
  • Save K3ndev/4b406d307661b55a10105ef88a8cb8a8 to your computer and use it in GitHub Desktop.
Save K3ndev/4b406d307661b55a10105ef88a8cb8a8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgresql.md

Setting Up PostgreSQL Inside Docker

  • docker pull postgres
  • docker run --name postgres-test -e POSTGRES_PASSWORD=root -d postgres
  • docker exec -it postgres-test psql -U postgres
  • to exit : \q

Creating a Database

CREATE DATABASE mydatabase;

-- Dropping a Database
-- DROP DATABASE mydatabase;

Connecting to a Database

  • \c mydatabase

Creating a Table

CREATE TABLE user_table(
  user_id SERIAL PRIMARY KEY,
  username TEXT
);

-- SERIAL = auto-increments
-- PRIMARY KEY = defines the primary key constraint for the table.

-- Dropping a Table
-- DROP TABLE user_table;

Inserting Data

INSERT INTO user_table(username) VALUES
('john'),
('jane');

Querying Data

SELECT * FROM user_table;

Updating Data

UPDATE user_table SET username = 'kenneth' WHERE user_id = 1;

Deleting Data

DELETE FROM user_table WHERE username = 'kenneth';

Enum

CREATE TYPE user_role AS ENUM ('admin', 'member');
CREATE TABLE user_role_table(
  user_role_id SERIAL PRIMARY KEY,
  user_role_name user_role UNIQUE
);

-- SELECT * FROM user_role_table;

INSERT INTO user_role_table(user_role_name) VALUES
('admin'),
('member');

Creating a Foreign Key

CREATE TABLE user_user_role_table(
  user_id SERIAL REFERENCES user_table(user_id),
  user_role_id SERIAL REFERENCES user_role_table(user_role_id)
);

INSERT INTO user_user_role_table(user_id, user_role_id) VALUES
(1,1),
(2,2);

SELECT * FROM user_user_role_table;

SELECT DISTINCT

SELECT DISTINCT (col) from table

-- return first unique data, remove duplicate

COUNT

-- with COUNT(*), will count even with null values
-- with COUNT(col), will count except with null value

SELECT COUNT(*) FROM table;
SELECT COUNT(DISTINCT (col)) FROM table;

SELECT WHERE

-- =, equal to 
-- <> or !=, not equal to
-- >, greater than
-- <, less than 
-- >=, greater than or equal to
-- <=, less than or equal to
-- BETWEEN, between a range of values 
-- LIKE, pattern matching using wildcards (often used with % and _)
-- IN, matches any value in a list
-- IS NULL, checks for null values
-- AND, OR, NOT, logical operators for combining multiple conditions

ORDER BY

SELECT * FROM table WHERE conditions ORBER BY col <ASC | DESC>

-- SELECT col1, col2 FROM table WHERE conditions ORBER BY col1 ASC,  col2 DESC,

LIMIT

SELECT * FROM table WHERE amount != 0 ORDER BY payment_DATA ASC LIMIT 0, 10
-- SELECT * FROM table WHERE amount != 0 ORDER BY payment_DATA ASC LIMIT 10, 20

BETWEEN

SELECT col FROM table WHERE table_data BETWEEN '2023-01-01' AND '2023-06-30'

IN and NOT IN

SELECT * FROM table WHERE col IN ('0.1', '0.2', '0.3')
-- SELECT * FROM table WHERE col NOT IN ('0.1', '0.2', '0.3')

LIKE and ILIKE

SELECT col FROM table WHERE col_name LIKE pattern
SELECT col FROM table WHERE col_name ILIKE pattern

-- % represents any sequence of characters (including zero characters)
-- _ represents a single character
-- [] represents a character class, allowing you to 

-- LIKE, 'apple%' will match 'applesauce' but not 'APPLE'
-- ILIKE, 'apple%' will match 'applesauce' and 'APPLE'

AGGREGATE FUNCTION

-- SUM
-- AVG
-- COUNT
-- MIN
-- MAX

GOURP BY

-- SPLIT, Divides rows into groups based on unique values in specified columns.
-- APPLY, Allows aggregate functions to be applied to each group independently.
-- COMBINE, Aggregates rows within each group into a single summary row.

-- SPLIT: Grouping by region
SELECT region
FROM orders
GROUP BY region;

-- APPLY: Calculating total amount per region
SELECT region, SUM(amount) AS total_amount
FROM orders
GROUP BY region;

-- COMBINE: Getting count and average amount per region
SELECT region, COUNT(*) AS num_orders, AVG(amount) AS avg_amount
FROM orders
GROUP BY region;

HAVING

-- Example of using HAVING clause
SELECT region, SUM(total_amount) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(total_amount) > 10000;

-- WHERE filters individual rows before grouping.
-- HAVING filters groups after grouping has been applied.

Row-Level Security (RLS)

-- DROP POLICY removes an existing RLS policy from a table
DROP POLICY IF EXISTS <Policy Name> ON <Schema Name>.<Table Name>;

-- CREATE POLICY defines a new RLS policy on a table
CREATE POLICY "<Policy Name>" ON "<Schema Name>"."<Table Name>"
AS <PERMISSIVE or RESTRICTIVE> FOR <type of User>
USING (condition);

-- notes 
-- PERMISSIVE: Allows access if any policy grants access.
-- RESTRICTIVE: Denies access if any policy denies access.
-- FOR <select, insert, update, delete, all>
-- Create a policy named "allow_select_for_managers" in schema "public" on table "orders"
CREATE POLICY "allow_select_for_managers" ON "public"."orders"
AS PERMISSIVE FOR SELECT
USING (role = 'manager');

ROLE

-- Syntax: CREATE ROLE <role_name> [ WITH <options> ];

-- Example: Creating a role named 'sales_user'
CREATE ROLE sales_user;

-- List roles with additional details
SELECT * FROM pg_roles;

options

-- Syntax: CREATE ROLE <role_name> [ WITH <options> ];

-- Example: Creating a role with options
CREATE ROLE sales_user WITH
    LOGIN                -- Allow role to log in
    PASSWORD 'password'  -- Set password for the role
    NOSUPERUSER          -- Do not grant superuser status
    CREATEDB             -- Allow role to create databases
    INHERIT              -- Role inherits privileges of roles it is a member of
    CONNECTION LIMIT 10; -- Limit maximum connections for the role

managing role

-- Granting a role to another role
GRANT sales_user TO sales_manager;

-- Revoking a role from another role
REVOKE sales_user FROM sales_manager;

Inner JOIN

  • The INNER JOIN keyword selects records that have matching values in both tables. testproducts table
-- result 
  testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
            10 | Mias Popular Ice        |          14

categories table

-- result
 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
SELECT testproduct_id, product_name, category_name
FROM testproducts
INNER JOIN categories ON testproducts.category_id = categories.category_id; 
-- result 
 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals

-- INNER is the default join type for JOIN

LEFT JOIN

  • The LEFT JOIN keyword selects ALL records from the "left" table, and the matching records from the "right" table.
SELECT testproduct_id, product_name, category_name
FROM testproducts
LEFT JOIN categories ON testproducts.category_id = categories.category_id; 

-- result
 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              2 | Marys Healthy Mix      |
              3 | Peters Scary Stuff     |
              4 | Jims Secret Recipe     |
              5 | Elisabeths Best Apples |
              6 | Janes Favorite Cheese  | Dairy Products
              7 | Billys Home Made Pizza |
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
             10 | Mias Popular Ice       |

RIGHT JOIN

  • The RIGHT JOIN keyword selects ALL records from the "right" table, and the matching records from the "left" table.
SELECT testproduct_id, product_name, category_name
FROM testproducts
RIGHT JOIN categories ON testproducts.category_id = categories.category_id; 

-- result
 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
                |                        | Condiments
                |                        | Meat/Poultry
                |                        | Beverages
                |                        | Produce

FULL JOIN

SELECT testproduct_id, product_name, category_name
FROM testproducts
FULL JOIN categories ON testproducts.category_id = categories.category_id; 
-- result
 testproduct_id |      product_name       | category_name
----------------+-------------------------+----------------
              1 | Johns Fruit Cake        | Confections
              2 | Marys Healthy Mix       |
              3 | Peters Scary Stuff      |
              4 | Jims Secret Recipe      |
              5 | Elisabeths Best Apples  |
              6 | Janes Favorite Cheese   | Dairy Products
              7 | Billys Home Made Pizza  |
              8 | Ellas Special Salmon    | Seafood
              9 | Roberts Rich Spaghetti  | Grains/Cereals
             10 | Mias Popular Ice        |
                |                         | Condiments
                |                         | Meat/Poultry
                |                         | Beverages
                |                         | Produce
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment