postgres inheritence etl table schema migration - ghdrako/doc_snipets GitHub Wiki

Inheritance, in this context, allows a database object to inherit both the structure and behaviour from other database objects. Specifically, table inheritance enables the creation of child tables that inherit the column attributes of a parent table, offering a clear and efficient way to model data hierarchies. In simpler terms, the child table incorporates all the columns of the parent table while also accommodating additional columns unique to itself.

  • performance improvements, especially when dealing with data that naturally exhibits hierarchical relationships. This streamlined data modelling enhances query efficiency and reduces the need for complex JOIN operations.
  • table inheritance minimizes maintenance efforts, as changes to the parent table’s schema automatically propagate to the child tables, ensuring data consistency and reducing the risk of errors.
# Create Parent Table 
CREATE TABLE books.author 
(
  id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 10000000 CACHE 1 ), 
   author_name text,  CONSTRAINT id_prikey PRIMARY KEY (id) 
);

# Create Child Table with INHERITS clause
CREATE TABLE books.book_list 
(
  book_id integer, 
  book_name text 
) INHERITS (books.author);

# select the data from Parent Table
select * from books.author;
# Following the Output of the above query
 id | author_name 

----+-----------

(0 rows)

# select the data from Child Table
select * from books.book_list;
# Following the Output of the above query
 id | author_name | book_id | book_name 
 ----+-------------+---------+----------- 
 (0 rows)

# Insert data to book_list table
INSERT INTO books.book_list (id, author_name, book_id, book_name) VALUES (1, 'TEST', 804, 'PGS'); 
INSERT INTO books.book_list (id, author_name, book_id, book_name) VALUES (1, 'REST', 805, 'PGS');
 # select the data from book_list Table
 select * from books.book_list;
 # Following the Output of the above query
 id | author_name | book_id | book_name 
 ----+-------------+---------+----------- 
 1 | TEST | 804 | PGS 
 1 | REST | 805 | PGS 
 (2 rows)
# select the data from Parent Table
 select * from books.author;
 id | author_name 
----+------------- 
 1 | TEST 
 1 | REST 
(2 rows)

# Insert data to author table
INSERT INTO books.author (author_name) VALUES ('sun'), ('moon');
# select the data from parent Table
select * from books.author;
 # Following the Output of the above query 
 id | author_name 
----+------------- 
 1 | SUN 
 2 | MOON 1 | TEST 
 1 | REST (4 rows)
# select the data from Child Table
select * from books.book_list;
# Following the Output of the above query
id | author_name | book_id | book_name 
----+-------------+---------+-----------
 1 | TEST | 804 | PGS 
 1 | REST | 805 | PGS 
 (2 rows)

# select the data from parent Table using ONLY Clause
 select * from ONLY books.author;
# Following the Output of the above query
id | author_name
----+-------------
 1 | SUN
 2 | MOON
(4 rows)

ONLY clause indicates that the query only retrieves the output from the author table.