Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL - Table Inheritance

This tutorial is still in progress.

SQL 1999 (also called SQL 3) was the fourth revision of the SQL database query language which first introduced table inheritance, but only allows for only single inheritance. PostgreSQL supports table inheritance, but I have not had the opportunity to use it much because MySQL still does not support it (at the time of writing this post) which is really unfortunate, because it is a really useful feature, and this blog post hopes to clearly explain why. https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa For this tutorial, I am using plural names for tables. I am on the fence on whether table names should be plural or not. E.g. the "user" or "users" table. I will just go with whatever will cause less friction with the people I work with at the time.

Steps

For this scenario, I am setting up a database of users who own assets. These assets are either files held on the server that other users can download, or they are links to other other places on the internet where the content can be read or downloaded.

If you haven't already, I recommend you install PostgreSQL to follow along with this tutorial.

Connect to your database:

sudo -u postgres psql

Create and connect to a fresh new database to test with:

create database test;
\connect test;

Lets create a table of users. These users will "own" assets, and be the only person that will have permission to edit/delete the assets once in the system.

CREATE TABLE "users" (
    uuid uuid NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (uuid)
);

Now create the table containing the "core" information about our assets. This is the information that both our files and our links share. In our case, each asset as an identifier, a name, and an owner.

CREATE TABLE "assets" (
    uuid uuid NOT NULL,
    name varchar(255) NOT NULL,
    owner uuid references "user"("uuid") ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (uuid)
);

Now lets create our files table. As stated earlier, files are assets, but they are also special because they have a local filepath, which is where the file is stored on the server for serving up:

CREATE TABLE "files" (
    filepath varchar(255)
) INHERITS (assets);

Now lets creat

CREATE TABLE "link" (
    url varchar(255)
) INHERITS (assets);

Promote and Demote

https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa

Constratints are not inherited

References

Last updated: 5th November 2020
First published: 5th November 2020