Insert_all with temporary table

rubyonrails-talk
1

We’re using a temporary table to capture a lot of data generated at run time, and then using the DB to move that data around into the non-temp tables of interest.

We’d like to populate this table via Rails AR, rather than raw SQL.

We’ve been able to wrap the temp table with a anonymous AR model, and do single record operations. But bulk operations via insert_all depend upon the unique indexes being known from Postgres schema inspection, which seems to specifically exclude temporary tables.

Anyone know of a work-around?

ActiveRecord::InsertAll#find_unique_index_for is depending upon connection.schema_cache.indexes(model.table_name).select(&:unique) which does not include the unique indexes for temp tables.

2

… or … alternatively to have a primary_key on your temporary table! :slight_smile:

Try doing this to create your temp table:

original_model = Widget

# Model to back the temporary table
class TempTable < ActiveRecord::Base; end

# Build out a temp table with a few columns that come from an existing model's table
ActiveRecord::Base.connection.execute("CREATE TEMP TABLE #{TempTable.table_name} AS SELECT id, name, description FROM #{original_model.table_name} WHERE 1 = 0;")

# Add a primary key to the temp table on the +id+ column
ActiveRecord::Base.connection.execute("ALTER TABLE #{TempTable.table_name} ADD PRIMARY KEY (id);")

and with this setup you can use ActiveRecord to perform the .insert_all() just fine:

TempTable.insert_all(original_model.select(TempTable.column_names).map(&:attributes))
1 Like
3

To perform an INSERT operation using a temporary table in SQL, you generally follow these steps:

  1. Create the temporary table.
  2. Insert data into the temporary table.
  3. Perform the insert operation from the temporary table into the target table.

Here’s an example demonstrating these steps. Suppose we have a target table named employees and we want to insert data from a temporary table.

Step 1: Create the Temporary Table

First, create a temporary table. The syntax for creating a temporary table varies slightly depending on the database system (e.g., MySQL, SQL Server, PostgreSQL, etc.).

sql

Copy code

CREATE TEMPORARY TABLE temp_employees (
    id INT,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);