Redshift技术问询：table1比table2多一列时如何无需指定列插入数据？

Got it, let's figure out how to solve this Redshift data migration problem you're stuck on. The insert into table1 select * from table2 fails because table1 has an extra column, so the column counts don't line up—and I totally get not wanting to type out every single column name manually, especially if these tables have dozens of columns. Here are two reliable workarounds:

1. Dynamically Generate the INSERT Statement Using Redshift System Tables #

Redshift keeps track of all table metadata in system tables, so we can leverage that to build the correct column list automatically without typing each one.

Run this query to generate your ready-to-use INSERT statement:

SELECT 'INSERT INTO table1 (' || 
       STRING_AGG(col.column_name, ', ') || 
       ') SELECT ' || STRING_AGG(col.column_name, ', ') || ' FROM table2;'
FROM information_schema.columns col
WHERE col.table_name = 'table2'
AND col.table_schema = 'your_schema'; -- Replace with your actual schema (e.g., public)

This pulls all column names from table2, formats them into the exact INSERT syntax that maps to table1's matching columns. Just copy the output of this query and execute it—this will insert all matching data without you listing every column.

Quick Adjustment if the Extra Column Needs a Value #

If the extra column in table1 isn't nullable and doesn't have a default value, tweak the query to include a placeholder for it:

SELECT 'INSERT INTO table1 (' || 
       STRING_AGG(col.column_name, ', ') || ', your_extra_column' || -- Replace with table1's extra column name
       ') SELECT ' || STRING_AGG(col.column_name, ', ') || ', NULL' || -- Or use a specific default value instead of NULL
       ' FROM table2;'
FROM information_schema.columns col
WHERE col.table_name = 'table2'
AND col.table_schema = 'your_schema';

2. Use a Temporary Table as an Intermediate Layer #

If you want more flexibility (like validating data first or modifying the extra column before insertion), use a temp table to match table1's structure:

• First, create a temp table that mirrors table2:

  CREATE TEMP TABLE temp_table AS SELECT * FROM table2 LIMIT 0;
  

• Add the extra column to match table1 (adjust the data type and default as needed):

  ALTER TABLE temp_table ADD COLUMN your_extra_column VARCHAR(50) DEFAULT NULL; -- Replace with your column's details
  

• Insert data into the temp table (including a value for the extra column):

  INSERT INTO temp_table SELECT *, NULL FROM table2; -- Swap NULL with your desired value if needed
  

• Now insert into table1 using SELECT * since the temp table matches its structure:

  INSERT INTO table1 SELECT * FROM temp_table;
  

Critical Notes to Remember #

• Always verify column order! Redshift uses column position when using SELECT *, so make sure table2's columns are in the same order as the corresponding columns in table1. The system table query preserves the metadata's column order, which should align if table1 was built by adding the extra column to table2's structure.
• If the extra column in table1 is non-nullable and has no default, you must provide a value for it—there's no way around this, even with dynamic SQL.

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