PostgreSQL CheatSheet

PostgreSQL is:

• open sourced
• supports both relational and non-relational (JSON) queries
• for dynamic websites and web applications

Why are there schemas in each database?

• To better organize tables, create modules, and use RBAC
• Public is the default schema
• Sometimes putting different organizations into different databases is not as efficient as putting them into different schemas of the same database. An example would be a SAAS company with hunderds or thousands of clients.

Connection and Basic Operations

# use psql to connect on command line:
$ psql -h localhost -p 5432 -U ps_user inno_wiki

# Get version
inno_wiki=# select version();

# Get current connection info
test_db=# \conninfo
You are connected to database "test_db" as user "ps_user" on host "localhost" (address "::1") at port "5432".

Database Operations

• create database options:
  • owner
  • tablespace (set the default tablespace / physical storage locations on the server)
  • template "template1" is the default; "template0" is a pristine template with only essential system objects
  • encoding sets encoding, default is "SQL_ASCII"
  • locale sets locale
  • connection limit max number of concurrent connections to the database. default is 115 (15 for super users and 100 for regular users)
  • allow_connections enables to disables connections to the database

# List all databases
inno_wiki=# \list
                                                   List of databases
   Name    |  Owner  | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules |  Access privileges  
-----------+---------+----------+-----------------+------------+------------+--------+-----------+---------------------
 ccj       | ps_user | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 dng       | ps_user | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
...

# Switch to a different database
inno_wiki=# \c ccj
You are now connected to database "ccj" as user "ps_user".

# Create a database
ccj=# create database test_db;
CREATE DATABASE 

# See the owner
ccj=# \list

# See current connections
test_db=# SELECT * FROM pg_stat_activity WHERE datname = 'test_db';
datid | datname | pid  | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event | state  | backend_xid | backend_xmin | query_id |                           query                           |  backend_type  
-------+---------+------+------------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+--------+-------------+--------------+----------+-----------------------------------------------------------+----------------
 16728 | test_db | 8532 |            |       10 | ps_user | psql             | ::1         |                 |       48814 | 2024-12-20 21:17:42.485645+00 | 2024-12-20 21:40:30.649138+00 | 2024-12-20 21:40:30.649138+00 | 2024-12-20 21:40:30.649141+00 |                 |            | active |             |          898 |          | SELECT * FROM pg_stat_activity WHERE datname = 'test_db'; | client backend
(1 row)

# Alter a database
test_db=# alter database test_db with connection limit = 120;
ALTER DATABASE

# Rename a database
test_db=# alter database test_db rename to test_db2;
ERROR:  current database cannot be renamed
# needs to connect to other database
test_db=# \c ccj
You are now connected to database "ccj" as user "ps_user".
ccj=# alter database test_db rename to test_db2;
ALTER DATABASE

# Delete a database
ccj=# drop database if exists test_db2
ccj-# ;
DROP DATABASE
ccj=# drop database if exists test_db;
NOTICE:  database "test_db" does not exist, skipping
DROP DATABASE

# Copy a database
ccj=# create database ccj2 with template ccj;
CREATE DATABASE

Schema Operations

• to access an object in a schema, you need to qualify the object by using schema_name.object_name
• database and schemas is a 1-to-N relation
• default schema is the public schema

# get current schema
ccj=# select current_schema();
 current_schema 
----------------
 public
(1 row)

# List schemas
ccj=# \dn

# show search path (the path postgresql uses to search for objects within a database). $user represents the schema that has the same name as the current user
ccj=# SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

# Create Schema
ccj=# create schema sales;
CREATE SCHEMA

# new schema is not in search path
ccj=# SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

# add sales schema to search paths:
ccj=# set search_path to sales, public;
SET
ccj=# SHOW search_path;
  search_path  
---------------
 sales, public
(1 row)

# to give user access to resources in schema that they do not own:
GRANT USAGE ON SCHEMA schema_name TO role_name;

# to allow user to create objects in schemas they do not own:
GRANT CREATE ON SCHEMA schema_name TO user_name;

Table Operations

# List tables
inno_wiki=# \dt
                List of relations
 Schema |          Name          | Type  |  Owner  
--------+------------------------+-------+---------
 public | admin_menu             | table | ps_user
 public | admin_operation_log    | table | ps_user
 public | admin_permissions      | table | ps_user
 public | admin_role_menu        | table | ps_user
 public | admin_role_permissions | table | ps_user
 public | admin_role_users       | table | ps_user
...



```sql
-- 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;

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;

#### Roles and RBAC Management
- Roles are not namespaced, they are unique with in a single database
```sql
# List all roles:
inno_wiki=# select * from pg_roles;
           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 pg_database_owner           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6171
 pg_read_all_data            | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6181
 pg_write_all_data           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  6182
 pg_monitor                  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
...


# Create role:
ccj=# create role ccj_owner;
CREATE ROLE

ccj=# create role ccj_login login password 'testPassword';
CREATE ROLE

# list all roles
ccj=# select rolname from pg_roles;

# list all roles created by current logged in account:
ccj=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 ccj_login | 
 ccj_owner | Cannot login
 ps_user   | Superuser, Create role, Create DB, Replication, Bypass RLS
 test

# Create supoeruser role
ccj=# create role inno_owner superuser login password 'innoPass1';
CREATE ROLE

# Create role with database creation permission
ccj=# create role dba createdb login password 'dbaPass1';
CREATE ROLE

# Create role with expiration
ccj=# create role inno_temp login password 'innoPass1' valid until '2025-01-01';
CREATE ROLE

# give connect database access to role
ccj=# grant connect on database inno to inno_temp;

# Alter role with connection limit
ccj=# alter role ccj_login connection limit 1000;
ALTER ROLE

ccj=# \du
                              List of roles
 Role name  |                         Attributes                         
------------+------------------------------------------------------------
 ccj_login  | 1000 connections
 ccj_owner  | Cannot login
 dba        | Create DB
 inno_owner | Superuser
 inno_temp  | Password valid until 2025-01-01 00:00:00+00
 ps_user    | Superuser, Create role, Create DB, Replication, Bypass RLS
 test       |

# Grant Privileges to roles:
# privileges include: select, insert, update, delete, truncate, or all

# Grant all priviledges to user
ccj=# grant all on admin_operation_log to ccj_owner;
GRANT

# Grant all priviledges on all tables in a schema to a role:
ccj=# grant all on all tables in schema public to ccj_owner;
GRANT

# Revoke a privilege:
ccj=# revoke select on all tables in schema public from ccj_owner;
REVOKE

# alter role
alter role calf superuser;
alter role calf nosuperuser;

# Drop role
DROP ROLE [IF EXISTS] target_role;

-- execute these statements in the database that contains
-- the object owned by the target role
REASSIGN OWNED BY target_role TO another_role;
DROP OWNED BY target_role;
-- drop the role
DROP ROLE target_role;


https://neon.tech/postgresql/postgresql-administration/postgresql-backup-database