Using multiple types of SQL tables

When working with SQL, there are a few different types of database tables that we can use.
The most common one that everyone is aware of is a persistent one created using `create table command.
Other types of tables include derived tables, temporary tables and virtual tables. Let us take a look at the these in the context of PostgreSql.
Note that these concepts apply to other flavors of SQL as well.

1. Persistent Tables #

These tables persist in the database and are created using the CREATE TABLE command, which creates a new, initially empty table in the current database.
Multiple parameters can be given to the command as well as defined in the documentation. For example, in order to create a simple table that holds customer names, we can use this syntax.

CREATE TABLE customers (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    first_name      varchar(40) NOT NULL,
    last_name       varchar(40) NOT NULL,
);

2. Temporary Tables #

Temporary tables are automatically dropped at the end of the current session (or even at the end of the current transaction if so specified). These can be created using the TEMPORARY or TEMP parameter with the CREATE TABLE command.
Data can be inserted, updated, removed, etc on these tables just like regular persistent tables as long as the table exists

3. Derived or Computed Tables #

These are tables that are computed using a query (or subquery). Some examples of these tables are ones generated using subquery or JOIN statements or a complex combination of multiple queries.
Here is a simple example of a computed table generated using a subquery (SELECT * from customers)

SELECT first_name FROM (SELECT * FROM customers) AS cust

4. Materialized Views #

Materialized views are persistent tables that can be created by querying other tables. These can be considered as "View Only" tables. The data can be refreshed on these tables as well if required.
These tables can be queried to fetch the data they hold, but the data cannot be directly updated. In order to update the data in such a view table, the underlying tables that hold the data need to be updated followed by a refresh on the materialized view.
Materialized views are used for various reasons like sharing limited amount of data with external users, simplifying a complex data structure for reporting, caching certain frequently accessed data, etc.
Here is an example of how to create a materialized view.

CREATE MATERIALIZED VIEW customers_view AS SELECT * FROM customers;

References #

1. PostgreSql Create Table
2. Table Expressions
3. Materialized View