temporary table in SQLITE
(1) By anonymous on 2022-03-23 22:05:44 [link] [source]
why is it important?
A temporary table, as its named implied, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction. To create a temporary table, you use the CREATE TEMPORARY TABLE statement. Is it possible that I have temporary data that gets deleted after some time in SQLITE something similar that exists in Postgres?
example
To create a temporary table, you use the CREATE TEMPORARY TABLE statement.
CREATE TEMPORARY TABLE temp_table_name(
column_list
);
The TEMP and TEMPORARY keywords are equivalent so you can use them interchangeably:
CREATE TEMP TABLE temp_table(
...
);
sqlite> CREATE TEMP TABLE mytemp(c INT);
CREATE TABLE
sqlite> SELECT * FROM mytemp;
c
---
(0 rows)
doubt
Have you guys ever thought about using temporary tables?
example 2
sqlite> DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days'");
reference
- https://www.postgresqltutorial.com/postgresql-temporary-table/
- https://www.postgresql.org/message-id/e26308d6-695d-d501-2938-c9b776fe63c6%40rodonnell.ie
- https://www.educba.com/postgresql-temporary-table/
- https://www.postgresql.org/message-id/73de336d-b5d6-03e0-06e9-7754f4d1eb6e%40aklaver.com
- https://www.postgresql.org/message-id/aeb0063f-c820-2970-2a3b-3820c9eef239%40gmail.com
- https://stackoverflow.com/questions/26046816/is-there-a-way-to-set-an-expiry-time-after-which-a-data-entry-is-automaticall
- https://www.the-art-of-web.com/sql/trigger-delete-old/
(3) By Keith Medcalf (kmedcalf) on 2022-03-23 23:30:06 in reply to 1 [source]
A temporary table, as its named implied, is a short-lived table that exists for the duration of a database session.
This is improperly stated. A temporary table, in the context in which you are using the term (a table created with CREATE TEMPORARY TABLE name or CREATE TEMP TABLE name or CREATE TABLE temp.name all have the same effect -- a permanent table is created in the temp schema of the database connection on which the command was issued.
When a database connection is created, a temp schema is created automatically when first required. When the connection is closed, the temp schema is destroyed (including anything put in the temp schema). The temp schema cannot be accessed other than by the connection to which it belongs.
SQLite3 has no concept of a "temporary table" such as one might encounter in other RDBMS systems. The keyword TEMPORARY and is contraction TEMP are merely indicators of the schema under which to create the table (that is, merely syntactic sugar for CREATE TABLE temp.<name> (ie, exactly the same as if you attached a temporary database called "fuzzbutz" and created a permanent table in that schema thusly:
ATTACH '' AS 'fuzbutz';
CREATE TABLE fuzbutz.<name>(...)...;
which would only be accessible by the connection on which the commands were executed, and the entire fuzbutz database will be destroyed when the connection is closed.
That is to say that the equivalent to the CREATE TABLE <schema>.<name>... could also be the statement CREATE <schema> TABLE <name>... except, of course, that is not the standard.
(4) By Keith Medcalf (kmedcalf) on 2022-03-23 23:42:19 in reply to 1 [link] [source]
DELETE FROM limiter WHERE timestamp < CURRENT_TIMESTAMP - INTERVAL '2 days'");
This also will not be accepted by SQLite3. The equivalent would be:
DELETE FROM limiter WHERE timestamp < datetime('now', '-2 days');
assuming, of course, that the timestamp contains a timestamp in UTC.