Data Engineering

The obvious and more well known SQL way to keep some transient state is via temp tables! They give some nice data type guarantees, performance, editor happiness to name a few benefits. But - don’t use them for high frequency use cases! A few temp tables per second might already be too much and a disaster might be waiting to happen…Because CREATE TEMP TABLE actually writes into system catalogs behind the scenes, which might not be directly obvious… And in cases of violent mis-use - think frequent, short-lived temp tables with a lot of columns, plus unoptimized and overloaded Autovacuum together with long-running queries - can lead to extreme catalog bloat (mostly on pg_attribute) and unnecessary IO for each session start / relcache filling / query planning. And it’s also hard to recover from without some full locking - so that for critical high velocity DB’s it might be a good idea to revoke temp table privileges altogether - for app / mortal users at least (not possible for superusers).

The 2nd most obvious way to keep some DB-side session state around would probably be to use more persistent normal tables, right? Already better than temp tables as no danger of bloating the system catalog, right? NO. Pushing transient data though WAL (including replicas and backup systems) is pretty bad and pointless and only to be recommended for tiny use cases. In the Postgres world, exactly for these kinds of transient use cases, special UNLOGGED tables should be used! Which can relieve the IO pressure on the system / whole cluster considerably. One of course just needs to account for the semi-persistent nature - and the fact that they won’t be private anymore. Meaning usage of RLS in case of secret data or just using some random enough keys to avoid collisions.

In PostgreSQL, a generated column is a special type of column whose values are automatically calculated based on expressions or values from other columns. A generated column is referred to as a computed column in the SQL Server or a virtual column in Oracle .

There are two kinds of generated columns: Stored: A stored generated column is calculated when it is inserted or updated and occupies storage space. Virtual: A virtual generated column is computed when it is read and does not occupy storage space.

A virtual generated column is like a view, whereas a stored generated column is similar to a materialized view. Unlike a material view, PostgreSQL automatically updates data for stored generated columns.

PostgreSQL currently implements only stored generated columns.

In PostgreSQL, a sequence is a database object that allows you to generate a sequence of unique integers. Typically, you use a sequence to generate a unique identifier for a primary key in a table. Additionally, you can use a sequence to generate unique numbers across tables. To create a new sequence, you use the CREATE SEQUENCE statement.

Listing all sequences in a database To list all sequences in the current database, you use the following query: SELECT relname sequence_name FROM pg_class WHERE relkind = 'S';

PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.

The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.

The following illustrates the syntax of the GENERATED AS IDENTITY constraint: column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

In this syntax: The type can be SMALLINT, INT, or BIGINT. The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error. The GENERATED BY DEFAULT instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

PostgreSQL allows a table to have more than one identity column. Like the SERIAL, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.

To fix the error, you can use the OVERRIDING SYSTEM VALUE clause as follows: INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES(2, 'Green');

Alternatively, you can use GENERATED BY DEFAULT AS IDENTITY instead.

Because the GENERATED AS IDENTITY constraint uses the SEQUENCE object, you can specify the sequence options for the system-generated values.

For example, you can specify the starting value and the increment as follows: DROP TABLE color; CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), color_name VARCHAR NOT NULL );

In this example, the system-generated value for the color_id column starts with 10 and the increment value is also 10.

The “Database as Code” Manifesto