无法访问SQL Server遗留库，PostgreSQL两表数据对比迭代求助

Hey there! Let's work through your problem of comparing two PostgreSQL tables in Ruby since you can't access the SQL Server legacy DB right now. You mentioned getting stuck on the iteration loop part—here's a step-by-step approach to handle that, along with code examples you can adapt.

First, let's align on the core goal: verify if the data in two PostgreSQL tables matches. Using the pg gem, we'll break this into fetching data, iterating through records, and comparing values.

1. Connect to PostgreSQL & Fetch Table Data #

First, we'll establish a connection and pull data from both tables. It's critical to order rows by a unique key (like a primary ID) so we compare the right records, and convert rows to hashes for easy column access.

require 'pg'

# Establish database connection (adjust params as needed)
pg_conn = PG.connect(
  host: "localhost",
  port: 5432,
  dbname: "myDB",
  user: "userxx",
  password: "Zazzz"
)

# Helper function to fetch ordered rows from a table
def fetch_table_data(conn, table_name, primary_key)
  # Use parameterized query if needed to avoid SQL injection (safe here for static table names)
  result = conn.exec("SELECT * FROM #{table_name} ORDER BY #{primary_key}")
  # Convert string keys to symbols for cleaner access
  result.map { |row| row.transform_keys(&:to_sym) }
end

# Replace with your actual table names and primary key column
table_1_data = fetch_table_data(pg_conn, "customers", "customer_id")
table_2_data = fetch_table_data(pg_conn, "customers_staging", "customer_id")

2. Iterate & Compare Rows #

There are two common scenarios to handle: rows that exist in both tables but have mismatched values, and rows that exist in one table but not the other. Here's a robust way to cover both:

Option A: Handle Missing Rows & Mismatches #

Using hash maps indexed by the primary key makes it easy to check for missing records and compare matching ones efficiently:

# Create hash maps for fast lookups by primary key
table_1_map = table_1_data.index_by { |row| row[:customer_id] }
table_2_map = table_2_data.index_by { |row| row[:customer_id] }

# Check for rows present in Table 1 but not Table 2
table_1_map.each_key do |id|
  unless table_2_map.key?(id)
    puts "⚠️ Row with ID #{id} exists in Table 1 but not in Table 2"
  end
end

# Check for rows present in Table 2 but not Table 1
table_2_map.each_key do |id|
  unless table_1_map.key?(id)
    puts "⚠️ Row with ID #{id} exists in Table 2 but not in Table 1"
  end
end

# Compare values for rows present in both tables
(table_1_map.keys & table_2_map.keys).each do |id|
  row_1 = table_1_map[id]
  row_2 = table_2_map[id]

  if row_1 != row_2
    puts "\n❌ Mismatch found for ID #{id}:"
    # List all columns with differing values
    (row_1.keys | row_2.keys).each do |column|
      if row_1[column] != row_2[column]
        puts "  #{column}: Table1 = #{row_1[column]}, Table2 = #{row_2[column]}"
      end
    end
  end
end

Option B: Simple Row-by-Row (If You Expect Equal Row Counts) #

If you're certain both tables have the exact same set of primary keys, you can zip the arrays and compare each pair directly:

table_1_data.zip(table_2_data).each_with_index do |(row1, row2), index|
  if row1 != row2
    puts "\n❌ Mismatch at row #{index + 1} (ID: #{row1[:customer_id]})"
    (row1.keys | row2.keys).each do |col|
      puts "  #{col}: #{row1[col]} vs #{row2[col]}" if row1[col] != row2[col]
    end
  end
end

3. Clean Up #

Don't forget to close the database connection when you're done:

pg_conn.close

Key Tips for Accuracy #

• Data Type Handling: The pg gem returns most values as strings. If you're comparing dates, numbers, or booleans, cast them to Ruby types first (e.g., row[:created_at] = Time.parse(row[:created_at])).
• Large Tables: If your tables are huge, fetching all rows into memory might cause issues. Use PostgreSQL cursors or process data in batches instead.
• SQL Injection Safety: If your table names are dynamic (not hardcoded), use parameterized queries to avoid risks.

That should get you past the iteration loop hurdle and let you practice table data comparison effectively!

内容的提问来源于stack exchange，提问作者Fatna