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;