PostgreSQL VALUES Clause: The Hidden Gem for Batch Updates
Let's say you need to run a query that generates some data that you later want to use to update records. Example:
query = Ticket
.joins(:transactions => :settlements)
.select(tickets.id, MIN(settlements.date))
.having(tickets.posted_date != MIN(settlements.date))
.group("tickets.id")
# Convert to VALUES format
values = query.map { |t| "(#{t.id}, '#{t.min}'::date)" }.join(',')
# Result will be:
# "(1, '2023-01-15'::date),(2, '2023-02-01'::date),(3, '2023-03-10'::date)"
Instead of doing multiple individual UPDATE statements or complex subqueries, you can use PostgreSQL's VALUES clause to create an inline temporary table for batch updates. Here's how:
update_sql = %{
UPDATE tickets
SET posted_date = source_data.new_value
FROM (
VALUES #{values}
) AS source_data(id, new_value)
WHERE tickets.id = source_data.id
}.squish
ActiveRecord::Base.connection.execute(update_sql)
What's happening here?
1. The VALUES clause creates an inline temporary table
2. We name it using AS source_data(id, new_value) to define column names
3. PostgreSQL matches rows using the WHERE clause
4. All updates happen in a single efficient operation
Benefits:
- Single SQL statement instead of multiple updates
- More efficient than individual queries
- Cleaner than complex subqueries with joins
- PostgreSQL can optimize it as a batch operation
- Reduces network round trips between app and database
Instead of:
UPDATE target_table SET col = 'value1' WHERE id = 1;
UPDATE target_table SET col = 'value2' WHERE id = 2;
UPDATE target_table SET col = 'value3' WHERE id = 3;
Perfect for:
- Data migrations
- Batch updates from external sources
- Any scenario where you need to update multiple rows with different values