Skip to main content

PostgreSQL

PostgreSQL is:

  • open sourced
  • supports both relational and non-relational (JSON) queries
  • for dynamic websites and web applications
-- Get Version
select version();

-- List all tables
select * from pg_catalog.pg_tables;

-- Inheritance
create table cities (
  name text,
  population real,
  altitude int
);

create table capitals (
  state char(2)
) inherits (cities); -- muliple inheritance is possible

-- Result:

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;
+----------+----------+
|name      | altitude |
+----------+----------+
|Las Vegas | 2174     |
+----------+----------+
|Mariposa  | 1953     |
+----------+----------+
|Madison   | 845      |
+----------+----------+

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;
+----------+----------+
|name      | altitude |
+----------+----------+
|Las Vegas | 2174     |
+----------+----------+
|Mariposa  | 1953     |
+----------+----------+
    

SELECT * FROM capitals;
+----------+----------+-----------+
|name      | altitude | state     |
+----------+----------+-----------+
| Madison  | 845      | Wisconsin |
+----------+----------+-----------+

-- Array Type
create table sal_emp (
  name text,
  pay_by_quarter integer[],
  schedule text[][]
);

-- array can be defined as a properly formatted string, or using ARRAY keyword
insert into sal_emp values
('Bill1', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'),
('Carol1', '{20000, 25000, 25000, 25000}', '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'),
('Bill2', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]),
('Carol2', ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

-- array is 1-indexed. Select all records where 1st item in pay_by_quarter != 2nd item in pay_by_quarter
select name from sal_emp where pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)


-- select the first 2 items -> first item from schedule
select schedule[1:2][1:1] from sal_emp where name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

-- select the first 2 items -> 2 items from schedule, [2] => [:2]
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

-- select the first 2 items -> second and rest items from schedule
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

-- select all items -> first item from schedule
SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

-- Get array dimensions
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims	( dimension )
------------
 [1:2][1:2]
(1 row)

-- Get array upper bound
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';	-- or array_lower

 array_upper	( upper and lower bound of a specified array dimension )
-------------
           2
(1 row)

-- Get array length. array_upper = array_length if indexing starts at 1 (array_lower = 1), but sometimes array_lower != 1, in that case, array_upper != array_length
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length	( length of a specified array dimension )
--------------
            2
(1 row)

-- Get number of total elements
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

 cardinality	( total number of elements across all dimensions )
-------------
           4
(1 row)

-- Update array. Can update whole array, or just an item in array
update sal_emp set pay_by_quarter = '{25000,25000,27000,27000}' where name = 'Carol';
update sal_emp set pay_by_quarter = array[25000,25000,27000,27000] where name = 'Carol';
update sal_emp set pay_by_quarter[4] = 15000 where name = 'Bill';
update sal_emp set pay_by_quarter[1:2] = '{27000,27000}' where name = 'Carol';


-- JSONB and JSON. JSONB is generally preferred, but JSON can be more suitable in some situations: need to preserve white space / formatting in logging, legacy systems. We'll use JSONB instead.
create table users (
  id serial primary key,
  profile jsonb
);

-- "->" return the json object; "-->" returns the json object and convert it to string; "@>" checks wheather one document contains another

-- get all profiles where country is USA, and extract the name field as text and return them
SELECT profile->>'name' AS name FROM users WHERE profile @> '{"country": "USA"}';

-- "->" can be chained:
select '{"name": "Alice", "agent": {"bot": true} }'::jsonb -> 'agent' -> 'bot';
-- returns true

select '{"name": "Alice", "agent": {"bot": true} }'::jsonb @> '{"agent": {"bot": false}}';
-- returns false

select '{"name": "Alice", "agent": {"bot": true} }'::jsonb @> '{"agent": {"bot": true}}';
-- return true

select '{"name": "Alice", "agent": {"bot": true} }'::jsonb -> 'agent' ->> 'bot' is not null;

select '{"name": "Alice", "agent": {"bot": true} }'::jsonb -> 'agent' ? 'bot';


-- jsonb_each: expands into a set of key-value pairs
select jsonb_each( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );

-- Result:
	jsonb_each
1	(name,"Alice")
2	(agent,"{"bot": true}")


-- jsonb_object_keys: extract the keys
select jsonb_object_keys( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );

-- Result:
	jsonb_object_keys
1	name
2	agent


-- jsonb_extract_path: returns a JSON object that is traversed by a “path”

select jsonb_extract_path( '{"name": "Alice", "agent": {"bot": true} }'::jsonb, 'agent', 'bot');

-- Result:
	jsonb_extract_path
1	true


-- jsonb_pretty: prettify
select jsonb_pretty( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );

-- Result:
{
    "name": "Alice",
    "agent": {
        "bot": true
    }
}



-- select all users whose metadata contains '{"country": "Peru"}'
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';

-- select all users whose metadata.country is peru
SELECT * FROM users WHERE metadata->>'country' = 'Peru';

-- select all the metadata.country from users
SELECT metadata->>'country' FROM users;

-- select all the users whose metadata.country is not null
SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

-- select all users whose metadata.company.name is Mozilla
SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";

-- select all users who has '{"company":{"name": "Mozilla"}}' in the metadata
SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';

-- Remembering @> operator checks containment in a JSONB column, you can query on an array like {"x": ["a", "b", "c"]"} by just passing {"x":["a"]} to the WHERE clause:
SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';

-- select all users whose metadata.countries are in (‘Chad’, ‘Japan’)
SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');

-- Insert into all users
UPDATE users SET metadata = '{"country": "India"}';

-- Use || operator to concatenate the actual data with the new data. It will update or insert the value
UPDATE users SET metadata = metadata || '{"country": "Egypt"}';

-- To remove an attribute
UPDATE users SET metadata = metadata - 'country';


-- Creating a GIN index on the JSONB column can significantly improve query performance, especially for large datasets:
CREATE INDEX idx_user_profile ON users USING GIN (profile);

-- Example: Implementing a Real-Time Analytics System
-- Consider a scenario where a company tracks user interactions on its website. Storing this data in a JSONB column allows for flexible and efficient analysis. For example, counting the number of users who clicked a particular item:
SELECT COUNT(*) FROM user_interactions WHERE event_data @> '{"event": "item_click", "item_id": 123}';

-- This query extracts the email address from a nested contact object and filters users who have 'English' in their language preferences.

SELECT id, profile->'contact'->>'email' AS email FROM users WHERE profile->'preferences'->'languages' ? 'English';

-- This updates the phone number in the user’s profile, demonstrating efficient data modification without rewriting the entire JSON structure.
UPDATE users SET profile = jsonb_set(profile, '{contact,phone}', '"123-456-7890"') WHERE id = 1;

-- Insert value into jsonb
UPDATE users SET profile = jsonb_insert(profile, '{contact,phone2}', '"123-456-7891"') WHERE id = 1;

-- Remove value from jsonb
UPDATE users SET profile = jsonb_remove(profile, '{contact,phone2}') WHERE id = 1;




-- HStore vs JSONB
-- The main differences between them are that: hstore stores key-value pairs as a flat string and only supports basic data types such as strings and null values. JSONB stores JSON data in a binary format and supports nested data structures, arrays, and complex data types.



-- Case sensitive search (be default)
select * from cars where model like '%d';

-- Case insensitive search
select * from cars where model ilike '%d';

-- Between
select * from cars where yaer between 1970 and 1980;

-- in and not in
select * from customers where country in ('Norway', 'Sweden');
select * from customers where country not in ('USA', 'Canada');

-- distince
select distinct country from customers;

-- limit and offset
select * from customers order by name limit 10 offset 20;

-- alias
select customer_name as COMPANY from customers;

-- Group by, Having
SELECT COUNT(customer_id), country 
FROM customers 
GROUP BY country 
HAVING COUNT(customer_id) > 5;

-- Exists
select customer.customer_name
from customers
where exists (
  select order_id
  from orders
  where customer_id = customers.customer_id
);

-- Any and All
select product_name
from products
where product_id = ANY (
  select product_id
  from order_details
  where quantity > 120
);

select product_name
from products
where product_id = ALL (
  select product_id
  from order_details
  where quantity > 10
);

-- Case
select product_name,
case
  when price < 10 then 'Low price product'
  when price > 50 then 'High price product'
else
  'Normal product'
end as "price category"
from products;

-- Views
create view myview as
  select name, temp_lo, temp_hi, prcp, date, location
  from weather, cities
  where city = name;
-- then call the view:
select * from myview;

-- Primary Key
create table users (
  id serial primary key,
  profile jsonb
);

-- Foreigh Key
create table cities (
  name varchar(80) primary key,
  location point
);

create table weather (
  city varchar(80) references cities(name),
  temp_lo int,
  tmep_hi int,
  prcp real,
  date date,
);

-- Transactions
BEGIN;
update accounts set balance = balance - 100.00 where name = 'Alice';
savepoint my_savepoint1;

update accounts set balance = balance + 100.00 where name = 'Bob';
rollback to savepoint my_savepoint1;

update accounts set balance = balance + 100.00 where name = 'Casy';
commit;
MySQLPostgreSQL
case sensitive in where clause?NoYes
primary key definitionAUTO_INCREMENT NOT NULL PRIMARY KEYSERIAL NOT NULL PRIMARY KEY
case insensitive searchdefaultILIKE '%d'
case sensitive searchSELECT * FROM your_table
WHERE column
COLLATE utf8mb4_bin='SearchTerm';		default
limit and offsetselect *
from products
limit 20, 10;

select *
from products
limit 10 offset 20;		select *
from products
limit 10 offset 20;
Group Byselect count (customer_id), country
from customers
group by country;		selecct count (customer_id), country
 from customers
group by country;
min, max, sum, pricesamesame
(window functions)
ACIDonly with InnoDBFull Compliance
Concurrency Control (MVCC)only with InnoDBFull Compliance
IndexesB-Tree and R-Treetrees, expression indexes, partial indexes, hash indexes
Data TypesPure relationalobject-relational, json, array, xml
Viewsbasicmaterialized views
Stored Proceduressupportedsame, but also supports other languages
Triggersonly AFTER and BEFORE for Insert, Update, Deletesame, but also INSTEAD OF trigger
Performancebetter if frequent readsbetter if frequent writes
Write PerformanceUses locks to prevent concurrent writesSupports concurrent writes
Read PerformanceSingle process for multiple usersEach user gets a process with 10MB memory
if, ifnullsupportednot supported, use case statement

SQL

-- Create Table
create table cars (
  brand varchar(255),
  model varchar(255),
  year int
);

-- Drop Table
drop table cars;

-- Truncat Table (delete all records)
truncate table cars;

-- Add Column
alter table cars add color varchar(255);

-- Change Column
alter table cars alter column year type varchar(4);
alter table cars alter column color type varchar(30);

-- Drop Column
alter table cars drop column color;

-- CRUD
insert into cars (brand, model, year) values ('Ford', 'Mustang', 1964);

select * from cars;
select brand, year from cars;

update cars set color='red' where brand='Ford';

delete from cars where brand = 'Ford';

-- Others
select * from cars where model like '%d';
select * from cars where model like '_ustang';
select * from cars where year between 1970 and 1980;
select * from cars where brand in ('Ford', 'Audi', 'BMW');
select * from cars where brand not in ('Ford', 'Audi', 'BMW');
select distinct brand from cars;
select * from cars limit 10 offset 20;
select brand as CAR_BRAND from cars

select count(brand), brand
from cars
group by brand
having count(brand) > 5;
Back to top