Skip to content

TODO

Test Database

DROP TABLE users;
CREATE TABLE IF NOT EXISTS users (username VARCHAR(50), phone_num VARCHAR(15), bio TEXT, email TEXT);

INSERT INTO users (username, phone_num, bio, email) VALUES ('user001', '11111111111', 'Hi I am user001', 'user001@test.com');
INSERT INTO users (username, phone_num, bio, email) VALUES ('user002', '11112222222', 'This is user 002', 'user002@test2.xyz');
INSERT INTO users (username, phone_num, bio, email) VALUES ('user003', '11113333333', 'I am here to ...', 'user003@test3.org');
INSERT INTO users (username, phone_num, bio, email) VALUES ('user004', '11114444444', 'Hi I am user004', 'user004@test4.net');

select * from users;

SELECT (SELECT regexp_matches(bio, 'Hi')) FROM users;


INSERT INTO users (username, phone_num, bio) VALUES ('user005', '1111555555', 'Hi I am user004 my website is https://randomest.blogspot.com/');

INSERT INTO users (username, phone_num, bio) VALUES ('user005', '1111555555', 'https://news.ycombinator.com/');


SELECT (SELECT regexp_matches(bio, '(?:(?:https?|ftp):\/\/)(?:\S+(?::\S*)?@)?(?:(?!(?:10|127)(?:\.\d{1,3}){3})(?!(?:169\.254|192\.168)(?:\.\d{1,3}){2})(?!172\.(?:1[6-9]|2\d|3[0-1])(?:\.\d{1,3}){2})(?:[1-9]\d?|1\d\d|2[01]\d|22[0-3])(?:\.(?:1?\d{1,2}|2[0-4]\d|25[0-5])){2}(?:\.(?:[1-9]\d?|1\d\d|2[0-4]\d|25[0-4]))|(?:(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)(?:\.(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)*(?:\.(?:[a-z\u00a1-\uffff]{2,}))\.?)(?::\d{2,5})?(?:[/?#]\S*)?')) FROM users;


SELECT (SELECT regexp_matches(body, '(?:(?:https?|ftp):\/\/)(?:\S+(?::\S*)?@)?(?:(?!(?:10|127)(?:\.\d{1,3}){3})(?!(?:169\.254|192\.168)(?:\.\d{1,3}){2})(?!172\.(?:1[6-9]|2\d|3[0-1])(?:\.\d{1,3}){2})(?:[1-9]\d?|1\d\d|2[01]\d|22[0-3])(?:\.(?:1?\d{1,2}|2[0-4]\d|25[0-5])){2}(?:\.(?:[1-9]\d?|1\d\d|2[0-4]\d|25[0-4]))|(?:(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)(?:\.(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)*(?:\.(?:[a-z\u00a1-\uffff]{2,}))\.?)(?::\d{2,5})?(?:[/?#]\S*)?')) FROM test_reddit_data;


SELECT (SELECT regexp_matches(email, '([^@]+)'  )) FROM users;

SELECT (SELECT regexp_matches(email, '@(.*)'  )) FROM users;

select substring(column_name from '.*://([^/]*)') as domain_name from table_name;

select substring(url from '.*://([^/]*)') as website_domain from test_reddit_comments;

/* redshift sql */
SELECT (SELECT regexp_substr(bio, '(?:(?:https?|ftp):\/\/)(?:\S+(?::\S*)?@)?(?:(?!(?:10|127)(?:\.\d{1,3}){3})(?!(?:169\.254|192\.168)(?:\.\d{1,3}){2})(?!172\.(?:1[6-9]|2\d|3[0-1])(?:\.\d{1,3}){2})(?:[1-9]\d?|1\d\d|2[01]\d|22[0-3])(?:\.(?:1?\d{1,2}|2[0-4]\d|25[0-5])){2}(?:\.(?:[1-9]\d?|1\d\d|2[0-4]\d|25[0-4]))|(?:(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)(?:\.(?:[a-z\u00a1-\uffff0-9]-*)*[a-z\u00a1-\uffff0-9]+)*(?:\.(?:[a-z\u00a1-\uffff]{2,}))\.?)(?::\d{2,5})?(?:[/?#]\S*)?')) FROM users;

SELECT (SELECT regexp_substr(bio, '(http|ftp|https):\/\/([\w_-]+(?:(?:\.[\w_-]+)+))([\w.,@?^=%&:\/~+#-]*[\w@?^=%&\/~+#-])')) FROM users;


SELECT (SELECT regexp_substr(bio, '/(?:(?:https?|ftp|file):\/\/|www\.|ftp\.)(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[-A-Z0-9+&@#\/%=~_|$?!:,.])*(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[A-Z0-9+&@#\/%=~_|$])/igm')) FROM users;

select substring(bio FROM '.*://([^/]*)') AS domain_name FROM users;

SELECT (SELECT regexp_substr(bio, '[^/]+\\.[^/:]+')) FROM users;

SELECT (SELECT regexp_substr(email, '([^@]+)'  )) FROM users;

SELECT (SELECT regexp_substr(email, '@(.*)'  )) FROM users;

SQL Query To Extract URL / Link From Text Content Of Postgres Database Table | Regex Extractor

Create Table

CREATE TABLE IF NOT EXISTS table_name (column_name_1 type, column_name_2 type);

Insert into Table

INSERT INTO table_name (column_name_1, column_name_2) VALUES (value1, value2)

FInd Duplicates

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Auto increment Primary Key

TODO

JOIN

SELECT * FROM table_1 JOIN table_2 ON \ 
    table_1.column_name_1 = table_2.column_name_2 ;


SELECT * FROM (transactions2 JOIN albums_sold ON \
                               transactions2.Transaction_Id = albums_sold.Transaction_Id JOIN \
                               employees ON transactions2.Employee_Id = employees.Employee_Id );


SELECT transactions2.transaction_id, transactions2.customer_name, \
                     employees.employee_name, transactions2.year, albums_sold.album_name, \
                     sales.amount_spent FROM transactions2\
                JOIN employees    ON transactions2.cashier_id     = employees.employee_id \
                JOIN albums_sold  ON transactions2.transaction_id = albums_sold.transaction_id \
                JOIN sales        ON transactions2.transaction_id = sales.transaction_id;

SUM

SELECT cashier_name, SUM(amount_spent) FROM cashier_sales GROUP BY cashier_name;

WHERE

Delete Table

DROP table table_name;

Constraints ("NOT NULL", "UNIQUE", "PRIMARY KEY")


CREATE TABLE IF NOT EXISTS customer_transactions (
    customer_id int NOT NULL, 
    store_id int NOT NULL, 
    spent numeric
);

CREATE TABLE IF NOT EXISTS customer_transactions (
    customer_id int NOT NULL UNIQUE, 
    store_id int NOT NULL UNIQUE, 
    spent numeric 
);

CREATE TABLE IF NOT EXISTS customer_transactions (
    customer_id int NOT NULL, 
    store_id int NOT NULL, 
    spent numeric,
    UNIQUE (customer_id, store_id, spent)
);

CREATE TABLE IF NOT EXISTS store (
    store_id int PRIMARY KEY, 
    store_location_city text,
    store_location_state text
);

CREATE TABLE IF NOT EXISTS customer_transactions (
    customer_id int, 
    store_id int, 
    spent numeric,
    PRIMARY KEY (customer_id, store_id)
);

Note that the last example here of a primary key is actually a composite key

PostgreSQL: Documentation: 9.4: Constraints

ON CONFLICT

CREATE TABLE IF NOT EXISTS customer_address (
    customer_id int PRIMARY KEY, 
    customer_street varchar NOT NULL,
    customer_city text NOT NULL,
    customer_state text NOT NULL
);

INSERT into customer_address (
VALUES
    (432, '758 Main Street', 'Chicago', 'IL'
);

INSERT INTO customer_address (customer_id, customer_street, customer_city, customer_state)
VALUES
 (
 432, '923 Knox Street', 'Albany', 'NY'
 ) 
ON CONFLICT (customer_id) 
DO NOTHING;


INSERT INTO customer_address (customer_id, customer_street)
VALUES
    (
    432, '923 Knox Street, Suite 1' 
) 
ON CONFLICT (customer_id) 
DO UPDATE
    SET customer_street  = EXCLUDED.customer_street;

Insert String Quotes

Correct

Where 
    pm.Alias = 'Toys''R''Us France'

WRONG

Where 
    pm.Alias = 'Toys'R'Us France'

Where 
    table_name_one.column_name_one in ('val_1', 'val_2') AND
    table_name_two.column_name_two = 'val3'

Distinct

SELECT DISTINCT column1, column2, ...
FROM table_name;

Group By

GROUP BY GROUPING SETS

GROUP BY CUBE

Order By

SUM

SELECT count(DISTINCT table_name.users)
FROM table_name

COUNT of DISTINCT

SELECT
  category,
  COUNT(*) AS `num`
FROM
  posts
GROUP BY
  category

SELECT
  district,
  COUNT(*) AS num
FROM
  address
GROUP BY
  district
ORDER BY
  num DESC
LIMIT 10

Limit

SELECT * FROM table_name LIMIT 3;

EXTRACT

COPY

PostgreSQL: Documentation: 9.1: COPY

distkey

sortkey

Truncate

IS NOT NULL

EXISTS

ETL

INSERT INTO table_in (val_in_1, val_in_2, val_in_3)
SELECT val_select_1, val_select_2, val_select_3
FROM table_select;

List Tables

select * from pg_catalog.pg_tables;

PostgreSQL List Tables | Listing Out Tables using SELECT Query

Sources