_edited_edited.png)
In this blog, we will see some miscellaneous concepts of Postgresql.
Topics covered include:
Materialized Views.
Updatable views with CASCADED CHECK OPTION.
Create Partition on a table and insert rows to check data is rightly partitioned.
Working with JSON data.
Create a Parametric Cursor.
Listing psql utilities along with returning the query every N seconds.
Create user defined -domain datatype for address, postal code, email, enum.
Create a custom index method.
Using GRANT/REVOKE for security using roles and users.
Write a custom / user-defined aggregate function.
Display hierarchy of employees under a given manager (using recursive view).
Using Explain Analyze to Optimize the query.
Materialized Views:
Creating Materialized View with no data, to display number of male and female patients:
A Material View (MatView) allows to store the result of a query physically and update the data periodically.
A MatView caches the result of a complex expensive query and then allows you to refresh this result periodically.
A MatView executes the query once and then holds onto those results for your viewing pleasure until you refresh the materialized view again in different ways.
This is opposed to a straight-up view, which then re-execute the query every time that you access the data in it. The data in a view is FRESH, but you're paying for the freshness in execution time, as interaction with server, connection to server, and running the query again happen. The main question in MatViews versus views is FRESHNESS of data versus PERFORMANCE time.
MatView with “WITH DATA” option → when we want to load data into MatViews at the creation time.
MatView with “WITH NO DATA” option → when we want to create an empty materialized view with no data.
MatView can also be used like a regular table, for example, you can add indexes or primary key on it, it supports VACUUM and ANALYZE commands (useful when you refresh it often).
MatView helps to easily refresh (CONCURRENTLY) / query without locking everyone else out of it. Also helps in reducing tedious tasks like updating a table without locking.
Quick tip: You can mess up your source table, your end-user won’t notice it before the refresh, as they access the MatView and not the actual table!
MatViews are generally used to cache results of a heavy query.
When we need to store data that has been manipulated from its basic normalized state, and that manipulation is expensive or slow, and you don’t mind that your data is stale. That manipulation can be a complicated join or union, filtering criteria, or protracted calculations etc.
WITH NO DATA → the view is flagged as unreadable. It means we cannot query data from view until we load data into it, using REFRESH MATERIALIZED VIEW mv_name. QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS gender_count_mview AS SELECT "Gender", COUNT("Patient_ID") FROM "Patients" JOIN "Gender" USING ("Gender_ID") GROUP BY "Gender" WITH NO DATA; SELECT * FROM gender_count_mview; – returns ERROR
EXPLANATION: — Refresh gender_count_mview to get data: REFRESH MATERIALIZED VIEW gender_count_mview; – -- runs materailized view again, first time we can use this. With this entire MatView is locked and we cannot query.
–OR below REFRESH … CONCURRENTLY helps data to be accessed while it’s updating - recommended every time –after first time, to keep data as many times and as accessible as possible. We can query on MatView, while it is updated. For this, MatView must have a UNIQUE index. – REFRESH MATERIALIZED VIEW CONCURRENTLY gender_count_mview; Note: Unless REFRESH command is executed, MatView data does not change) -- then run select command
SELECT * FROM gender_count_mview;
Checking if a MatView is populated or not
SELECT relispopulated FROM pg_class WHERE relname = ‘gender_count_mview’;
Updatable Views with CASCADED CHECK OPTION:
A regular view does not store data physically and always gives updated data. An updatable view allows you to update data on underlying data. Rules are:
Query (inside view) must have one FROM entry which can be either a table or another updatable view
Query cannot contain DISTINCT, GROUP BY, WITH, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, HAVING at top level.
Selection list in query inside view cannot have any window function, any set-returning function, any aggregate function such as SUM, COUNT, AVG, MIN, MAX. We are only doing update on view and not selecting columns on an updatable view.
Use following operations to update the data: INSERT, UPDATE, DELETE along with a WHERE clause.
When you perform update operations, user must have corresponding privilege on the view, but don't need to have privilege on underlying table. Helps in securing database. QUERY: -- create a table patients_country CREATE TABLE patients_country (patient_id SERIAL, country_code varchar(4), city_name varchar(50)); INSERT INTO patients_country (country_code, city_name) VALUES ('US', 'Boston'),('US','Chicago'),('US','California'); -- create view on patients_country, where city_name like 'C%' CREATE OR REPLACE VIEW v_patients_c AS SELECT patient_id, country_code, city_name FROM patients_country WHERE city_name LIKE 'C%'; SELECT * FROM v_patients_c; -- create view where all cities LIKE 'C%' and country_code = 'US' CREATE OR REPLACE VIEW v_patients_c_us AS
SELECT patient_id, country_code, city_name FROM v_patients_c WHERE country_code = 'US' WITH CASCADED CHECK OPTION; SELECT * FROM v_patients_c_us;
-- invalid data => NOT inserted in v_patients_c_us view and NOT inserted in patient_country table.
INSERT INTO v_patients_c_us (country_code, city_name) VALUES ('US','Seattle');
-- valid data => entered into patient_country table, and shown in v_patients_c_us
INSERT INTO v_patients_c_us (country_code, city_name) VALUES ('US','Connecticut');
SELECT * FROM v_patients_c_us;
SELECT * FROM patients_country;
Create Partition on a table and insert rows to check data is rightly partitioned.
Partitioning a very large table on frequently accessed columns, helps improve the performance of query to a very very large extent.
-- create table with partition range type
-- (Partition by List, Partition by Hash are other Parition types)
CREATE TABLE patients_range(
id bigserial,
birth_date DATE NOT NULL,
country_code VARCHAR(2) NOT NULL
) PARTITION BY RANGE(birth_date);
SELECT * FROM patients_range;
-- create partition tables
CREATE TABLE patients_range_y2000 PARTITION OF patients_range
FOR VALUES FROM ('2000-01-01') TO ('2001-01-01');
CREATE TABLE patients_range_y2001 PARTITION OF patients_range
FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');
-- insert data into main table
INSERT INTO patients_range(birth_date, country_code) VALUES
('2000-01-01','US'), ('2000-01-02','US'), ('2000-12-31','US'), ('2001-01-01','US');
SELECT * FROM patients_range; -- returns all 4 records
SELECT * FROM ONLY patients_range; -- returns no records
SELECT * FROM ONLY patients_range_y2000; -- returns 1 row
SELECT * FROM ONLY patients_range_y2001; -- returns 3 rows
-- Update operations
UPDATE patients_range SET birth_date = '2001-10-10' WHERE id = 1;
SELECT * FROM patients_range; -- will have 4 records with id1 data changed
SELECT * FROM ONLY patients_range_y2000; -- returns 2 rows
SELECT * FROM ONLY patients_range_y2001; -- returns 2 rows
--Explain analyze query tool
SELECT * FROM patients_range;
SELECT * FROM patients_range WHERE birth_date = '2000-01-02'; -- on partitioned column
-- does not scan all table, but scans just the partition patients_range_y2000,
-- as can be seen from explain analyze tool
---------------------------------------------------------------------------------------------------
Working with JSON data
Create a parametric cursor
Listing psql utilities along with returning the query every N seconds.
Create user defined - domain datatype for address, postal code, email, enum.
Create a custom index method.
Using GRANT / REVOKE for Security using roles and users.
Write a custom / user-defined aggregate function.
Create a trigger to raise notice and prevent the deletion of a record from a view
Display hierarchy of employees under a given manager. (using recursive view)
Using Explain Analyze to Optimize the query.
Hope you enjoyed learning various concepts provided by PostgreSQL.
Happy Learning!!!
Comments