Features and usage of the temporary table (temporary table) in PostgreSQL

People familiar with Oracle are not unfamiliar with temporary tables (temporary table), many scenarios have a good effect on solving problems, and the concept of temporary tables in open Source Library PostgreSQL, although the same name as temporary tables in Oracle, There are similar areas of use and features, but there are many different aspects, and the following is a comparison of examples of temporary tables in Oracle.

1. Session Isolation of Postgresql temp table

Temporary tables in Oracle, when created, although the data between sessions is isolated from each other, that is, one session does not see data for other sessions, but the definitions are shared. While the temporary table in PostgreSQL, after creation, not only the data between the different sessions are isolated from each other, even the definition of the temporary table is isolated from each other, that is, a session created temporary table, can not be seen by other sessions, as shown in:

Session1: Created a temporary table t_tmp.

Session2: Log in to the same user name and database as another session, view the temporary table created in Session1, neither query the table nor view the table structure, but you can see the presence of the temporary table from the system view.

2, posgresql temporary table of volatile

Temporary tables in Oracle, where the data for a session disappears automatically when the session exits, but once the definition of the temporary table is created, it will already exist, knowing that the user is deleted. The temporary table for PostgreSQL is not so, although the session exit session data will also disappear automatically, but the definition will also disappear as the session exits, that is, the temporal table in PostgreSQL is the longest lifetime of the session life cycle, or even shorter,:

is the temporary table t_tmp created before logging in to the query again after Session1 exited the session, which is not already found, even if it is a temporary table definition.

In addition, when creating a PostgreSQL temporary table, you can also set the relevant options so that the temporary table disappears at the end of the transaction, even if the session is not over, and disappears:

, although the creation of a temporary table was successful, but looked back to see how not to see the table definition, although the successive creation of the two sides, or do not see the table definition, can not be checked, because the definition of the temporary table is using the On Commit drop option, which means that the end of the transaction, the automatic deletion of the temporary table, In my Psql client tool, whenever a DDL or DML is completed, the transaction end is considered by default, so the DDL statement that creates the temporary table is considered a transaction, and although the creation of the statement succeeds, the table is created, but then, after the transaction is finished, it is quickly deleted again, so There is no way to see the definition of a temporary table anyway. However, if you explicitly declare the start and end of a transaction, even if you use the On Commit drop option, you can see the definition after the temporary table is created, and after inserting the data, you can see the data as shown in the following:

Then, using the temporary table created with the On commit drop option, once the transaction that created it ends, the temporary table and the data in it disappear, as shown in:

3. The volatility of PostgreSQL temporary table data

Data for temporary tables in Oracle automatically disappears as the session transaction or session ends, primarily when creating temporary tables. The data in the PostgreSQL temp table also has a similar function, except that by using the On Commit drop option, you can set the transaction end temp table to disappear, and you can control the data for the temporary table to disappear at the end of the transaction by using the On Commit correlation option (only the data disappears. The definition also exists) and the session end disappears (both the data and the table definition disappear), and the temporary table is defined as follows on commit delete rows:

As you can see, although the temporary table was successfully created and the data was successfully inserted into it, when querying the data, although the table definition is still there, the data is missing because the definition uses the On commit delete rows option, where the transaction ends and the data disappears, and the insert The INTO statement defaults to a transaction, and when the transaction is finished, the inserted data disappears immediately, such as explicitly defining the start and end of the transaction, and the inserted data can be found during the transaction:

But once the transaction is over, the data that has just been inserted into the temporary table is immediately gone, such as:

In addition, you can use the on Commit Preserve rows option to define a temporary table, the temporary table defined by this option, and the data will still exist at the end of the transaction until the end of the session, as shown in:

As you can see, after the insert transaction for a single statement is complete, the existence of the data is still available, which is also the default behavior of the temporary table definition when the on commit option is not set.

Features and usage of the temporary table (temporary table) in PostgreSQL