We will login as adminstrators, but we will set VOC shema as default. Password is "monetdb".
mclient -u monetd -d voc; SET SCHEMA voc;
CREATE TABLE permTable ( Number INTEGER ); INSERT INTO permTable ( Number ) VALUES ( 1 ), ( 2 );
Temporary Tables
After the execution of the query, if the result is not saved in a table or sent to an application, the result of a query will be discarded. Queries are transient, but tables are permanent. Tables will permanently save data stored in them. Between queries and tables, we have temporary data structures called temporary tables. These structures are used to store session-specific data that only needs to exist for a short duration.
Creation of a Local Temporary Table
We will create a temporary table that will exist only during one session. Such temporary tables are called LOCAL temporary tables.Default behavior of temporary tables is to lose their content at the end of transaction. We can prevent that with option on commit PRESERVE ROWS. We don't want the temporary table to be emptied at the end of the transaction because we want to observe the behavior of the table.
CREATE LOCAL TEMPORARY TABLE tempTable ( Number INTEGER PRIMARY KEY ) ON COMMIT PRESERVE ROWS; INSERT INTO tempTable ( Number ) VALUES ( 3 ), ( 4 ); SELECT * FROM tempTable;
Temporary tables are created in "tmp" schema. We don't have to prefix their names with "tmp".
SELECT * FROM tmp.tempTable;
We can not create a temporary table in some schema other than "tmp".
Permanent table and temporary table can have the same name because they are in different schemas. I will create one temporary table with the name "permTable". CREATE LOCAL TEMPORARY TABLE permTable ( Number INTEGER ); If we read from the "permTable" without specifying schema, we will get the temporary table. SELECT * FROM permTable; For reading from the permanent table with the same name, we have to use fully qualified name. SELECT * FROM voc.permTable; So, temporary table has priority, although our current schema is "voc" (and not "tmp").
We can not create permanent objects in "tmp". SET SCHEMA tmp; CREATE TABLE ZZZ ( Number INTEGER ); SET SCHEMA voc;
Usage of a Temporary Table
It is possible to create queries that combine temporary and normal tables. SELECT * FROM voc.permTable JOIN tempTable ON permTable.Number <= tempTable.Number;
We can have constraints on the temporary table. In this case we have PK constraint. INSERT INTO tempTable ( Number ) VALUES ( 2 );
It is possible to export data from temporary table into CSV. COPY SELECT * FROM tempTable INTO '/home/fffovde/Desktop/tblCSV';
It is possible to import data from a CSV file into temporary table. COPY INTO tempTable ( Number ) FROM '/home/fffovde/Desktop/tblCSV'( Number );
It is possible to use UPDATE and DELETE on temporary tables.
UPDATE tempTable SET Number = 6 WHERE Number = 5; DELETE FROM tempTable WHERE Number = 6;
We can NOT alter our temporary table. ALTER TABLE tempTable ADD COLUMN Letter VARCHAR(10);
ALTER TABLE tempTable DROP CONSTRAINT temptable_number_pk;
It is possible to create a view on a temporary table. CREATE VIEW voc.View1 ( Number ) AS SELECT * FROM tmp.tempTable; SELECT * FROM View1;
It is not possible to create foreign key constraint on the permTable if it references tempTable. ALTER TABLE voc.permTable ADD CONSTRAINT FromTempTableConstraint FOREIGN KEY ( Number ) REFERENCES tmp.tempTable ( Number );
Info About Temporary Tables
We can not get statistics about our temporary table. SELECT * FROM sys.statistics( 'tmp','temptable');
We can find our temporary table in the system catalog. SELECT * FROM sys.tables WHERE Name = 'temptable';
Visibility of Local Temporary Table
I will log in to MonetDB from another instance of mclient, as "voc" user (password "voc"). mclient -u voc -d voc I will try to read local table "tempTable". SELECT * FROM tempTable; This will not work, because local temporary table are visible only in the session where they are created. Other users can not see these tables.
As the user "voc" I will create temporary table with the same name -"tempTable". This will be successful. Each user can have its own local temp table. CREATE LOCAL TEMPORARY TABLE tempTable ( Number INTEGER );
Even if we don't explicitly drop the table, our table will disappear after we log out of the current session. I'll log out of the session (monetdb user session), and after I log back in, "tempTable" will no longer exist. quit mclient -u monetdb -d voc SELECT * FROM tempTable;
We can terminate our "tempTable" by dropping it explicitly, even before the end of the session.
DROP TABLE tempTable;
ON COMMIT DELETE ROWS
"ON COMMIT DELETE ROWS" subclause means that after each transaction, data will be deleted. This is default behavior.
CREATE LOCAL TEMPORARY TABLE tempTable ( Number INTEGER PRIMARY KEY ) ON COMMIT DELETE ROWS; INSERT INTO tempTable ( Number ) VALUES ( 3 ), ( 4 ); SELECT * FROM tempTable;
In this case, temporary table is useful only inside of transaction. START TRANSACTION; INSERT INTO tempTable ( Number ) VALUES ( 3 ), ( 4 ); SELECT * FROM tempTable;
COMMIT;
AS SELECT
We can create temporary table based on some other table. Base table can be temporary or normal table.
CREATE LOCAL TEMPORARY TABLE tempTable2 ( Number ) AS ( SELECT Number FROM tempTable );
CREATE LOCAL TEMPORARY TABLE tempTable3 ( Number ) AS (SELECT Number FROM voc.permTable );
By defult we will use "WITH NO DATA". If try to read from tempTable3, we'll get empty table. SELECT * FROM tempTable3;
The "WITH DATA" subclause will help us inherit data. CREATE LOCAL TEMPORARY TABLE tempTable4 ( Number ) AS ( SELECT Number FROM voc.permTable ) WITH DATA ON COMMIT PRESERVE ROWS; SELECT * FROM tempTable4;
Global Temporary Tables
Global temporary tables are somewhere between normal tables and local tables. Their definition ( columns and data types ) is permanent. Name of the global table has to be unique in the "tmp" schema. Only users with authorization over "tmp" schema can create global temporary tables. In our example administrator "monetdb" can create global temporary tables, but "voc" user can not.
The thing that makes these tables temporary is their data. All the rows of the global temporary tables will be deleted after each transaction (for ON COMMIT PRESERVE ROWS) or after the session (ON COMMIT DELETE ROWS).
While definition of the global temporary tables is shared, data is not. Data placed in the global table by one user can not be seen by another user. So, global temporary table is a playground where each user can play with his own data.
Global temporary tables have similar characteristics as local temporary tables. We can use SELECT, DELETE, UPDATE. We can export them to CSV file. We can NOT alter global tables. We can create views on them. So, everything is the same as for local temporary tables.
It is possible to get statistics about global tables. SELECT * FROM sys.statistics( 'tmp','permtable');
Creation of the Global Temporary Table
We create global temporary table with similar statement as for the local temporary tables.
CREATE GLOBAL TEMPORARY TABLE globTable ( Number INTEGER PRIMARY KEY ) ON COMMIT PRESERVE ROWS;
This will fail for the "voc" user who doesn't have enough privileges over "tmp" schema.
Privileged users can successfully create global temporary table, but not if the table with such name already exist. It is not possible for two users to create global tables with the same names.
Visibility of Local Temporary Table
We will insert some data in our global temporary table. INSERT INTO globTable ( Number ) VALUES ( 5 ), ( 6 ); SELECT * FROM globTable;
If we try to read our table from the session of the "voc" user, we will see empty table. This show us that definition of a table is shared, but data is not shared. SELECT * FROM globTable;
Although "voc" user can not create global table, it can use tables created by others. "Voc" user can play with his own data. INSERT INTO globTable ( Number ) VALUES ( 7 ), ( 8 ); SELECT * FROM globTable;
When to Use Temporary Tables
You can create an excerpt from some big table. After that, you can run you queries on that smaller table, instead of the big one.
Because temporary tables are volatile and data is isolated between users, temporary tables are great for experiments.
Temporary tables shouldn't be used as a temporary step in queries. In that case, it is much wiser to use CTEs.
