如何在Redshift中导入缺失列的CSV并自动填充NULL值？

Absolutely! You can make this work with a small adjustment to your COPY command—Redshift does support auto-filling missing columns with NULL, but you need to explicitly map the columns present in your CSV to the target table.

By default, if the number of columns in your CSV doesn't match the number of columns in myTable, Redshift will throw an error. To avoid this, you just need to specify which columns from the CSV should be loaded into the table.

Adjusted COPY Command for 3-Column CSV #

Here's how to modify your command to handle the CSV with only col1, col2, col3:

COPY myTable (col1, col2, col3) FROM 'file.csv' CSV DELIMITER AS ',' IGNOREHEADER AS 1;

How This Works #

When you explicitly list the columns to load, Redshift understands exactly which CSV columns correspond to which table columns. Any columns in myTable that aren't included in the list (in this case, col4) will automatically be populated with NULL values—but only if that column allows NULLs in your table schema.

Key Notes #

• Check column nullability: Make sure col4 in myTable is defined as NULL (not NOT NULL). If it's set to NOT NULL, Redshift will reject the load because it can't insert a NULL into a non-nullable column.
• Handle column order mismatches: This approach also works if your CSV columns are in a different order than the table. Just list the CSV columns in their actual order, mapped to the correct table columns. For example, if your CSV has col2, col1, col3, you'd write:

  COPY myTable (col2, col1, col3) FROM 'file.csv' CSV DELIMITER AS ',' IGNOREHEADER AS 1;
  

Example Valid Table Schema #

For reference, here's a schema where col4 allows NULL values (critical for this to work):

CREATE TABLE myTable (
  col1 INT,
  col2 VARCHAR(100),
  col3 DATE,
  col4 DECIMAL(10,2) NULL  -- Allows auto-filled NULLs
);

If you have a mix of CSV files (some with 4 columns, some with 3), you'll just need to adjust the column list in the COPY command based on the file you're loading. There's no single command that auto-detects column count, but explicit column mapping is a clean, reliable solution for this scenario.

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