SQLite User Forum

Using a temp table with the same name as a main table
Login
Hierarchical

Using a temp table with the same name as a main table

(1) By Jeff M (jeffrey) on 2022-02-18 23:43:09 [source]

I want to know if this scenario is okay (supported in SQLite).

TL;DR: can I rely on a temp table (if it exists) overriding/hiding the data in a main table of the same name?

Details: A section of my app has some SELECT statements that, at different times, need to fetch data from one of two databases (database "A" or "B"). It would be difficult to change the existing code to SELECT from one of two different tables. There are about 15 tables involved. The databases have the same schema.

To handle this situation, I CREATE temporary tables in "main" that have the same names as the actual tables in "main," but are populated with data SELECTed from "B". "B" is an ATTACHED database. The actual tables in "A" are not touched.

When the SELECTs execute, they draw from the temporary tables if they exist, otherwise they draw from the actual tables. So, each SELECT either gets "B" data (if it the temp tables exist) or "A" data from main (if the temp tables do not exist).

To revert back to "A" (when I'm done with "B"), I do "DROP TABLE IF EXISTS temp.tableName".

An example sequence is:

  1. ATTACH DATABASE 'path_to_MyDataB' AS DataB;

  2. There are about 15 CREATE statements, one of which is:
    CREATE TEMP TABLE IF NOT EXISTS temp.Vacations AS SELECT * FROM DataB.Vacations;

  3. There are a bunch of SELECT statements, one of which is:
    SELECT duration FROM Vacations WHERE theDate = 100;
    (Note that the table name is "Vacations", not "temp.Vacations".)

  4. Then, about 15 DROP TABLE statements, one of which is:
    DROP TABLE IF EXISTS temp.Vacations;

  5. Later: DETATCH DataB;

This works, but I don't find any documentation specifically about using the same name and having a temp table hide the main table. Is my scheme okay and supported? Is there a better way?

(2) By anonymous on 2022-02-19 15:07:50 in reply to 1 [link] [source]

The order is temp, main, and attached databases in order they were attached.

See https://www.sqlite.org/lang_naming.html second paragraph.

(3) By Keith Medcalf (kmedcalf) on 2022-02-19 18:09:02 in reply to 1 [link] [source]

There are quite a number of things that are permitted only in the temp schema and not anywhere else, primarily for security reasons. After all, if the schema definition is located in the temp schema of a connection, then the only way it can have gotten there is by the programmers' explicit action after creating the connection.

Other than temp most cross-schema statements are disallowed. This is because, unlike other RDBMS systems that do allow cross-schema references, SQLite3 does not connect to more than one schema at a time, and that schema must be internally consistent. Compare this to other RDBMS systems where you open a connection to the database and multiple schema's are connected by default.

Also, for security reasons, some functions and virtual tables can be marked as requiring "direct access". That means that they may be referred to only in direct SQL statements, and not in a schema other than temp. SInce the only way such statements could have gotten into temp is because the programmer put them there, then the security (against some not-nice person putting them in a database schema without the programmers knowledge) does not apply.