Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

ALTER TABLE APPEND

Appends rows to a target table by moving data from an existing source table. Data in the source table is moved to matching columns in the target table. Column order doesn't matter. After data is successfully appended to the target table, the source table is empty. ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated.

Columns with the same names must also have identical column attributes. If either the source table or the target table contains columns that don't exist in the other table, use the IGNOREEXTRA or FILLTARGET parameters to specify how extra columns should be managed.

You cannot append an identity column. If both tables include an identity column, the command fails. If only one table has an identity column, include the FILLTARGET or IGNOREXTRA parameter. For more information, see ALTER TABLE APPEND Usage Notes.

Both the source table and the target table must be permanent tables. Both tables must use the same distribution style and distribution key, if one was defined. If the tables are sorted, both tables must use the same sort style and define the same columns as sort keys.

An ALTER TABLE APPEND command automatically commits immediately upon completion of the operation. It cannot be rolled back. You cannot run ALTER TABLE APPEND within a transaction block (BEGIN ... END).

Syntax

ALTER TABLE target_table_name APPEND FROM source_table_name 
[ IGNOREEXTRA | FILLTARGET ]

Parameters

target_table_name

The name of the table to which rows will be appended. Either specify just the name of the table or use the format schema_name.table_name to use a specific schema. The target table must be an existing permanent table.

FROM source_table_name

The name of the table that provides the rows to be appended. Either specify just the name of the table or use the format schema_name.table_name to use a specific schema. The source table must be an existing permanent table.

IGNOREEXTRA

A keyword that specifies that if the source table includes columns that are not present in the target table, data in the extra columns should be discarded. You cannot use IGNOREEXTRA with FILLTARGET.

FILLTARGET

A keyword that specifies that if the target table includes columns that are not present in the source table, the columns should be filled with the DEFAULT column value, if one was defined, or NULL. You cannot use IGNOREEXTRA with FILLTARGET.

ALTER TABLE APPEND Usage Notes

ALTER TABLE APPEND moves only identical columns from the source table to the target table. Column order doesn't matter.

If either the source table or the target tables contains extra columns, use either FILLTARGET or IGNOREEXTRA according to the following rules:

  • If the source table contains columns that don't exist in the target table, include IGNOREEXTRA. The command ignores the extra columns in the source table.

  • If the target table contains columns that don't exist in the source table, include FILLTARGET. The command fills the extra columns in the source table with either the default column value or IDENTITY value, if one was defined, or NULL.

  • If both the source table and the target table contain extra columns, the command fails. You cannot use both FILLTARGET and IGNOREEXTRA.

If a column with the same name but different attributes exists in both tables, the command fails. Like-named columns must have the following attributes in common:

  • Data type

  • Column size

  • Compression encoding

  • Not null

  • Sort style

  • Sort key columns

  • Distribution style

  • Distribution key columns

You cannot append an identity column. If both the source table and the target table have identity columns, the command fails. If only the source table has an identity column, include the IGNOREEXTRA parameter so that the identity column is ignored. If only the target table has an identity column, include the FILLTARGET parameter so that the identity column is populated according to the IDENTITY clause defined for the table. For more information, see DEFAULT.

ALTER TABLE APPEND Examples

Suppose your organization maintains a table, SALES_MONTHLY, to capture current sales transactions. You want to move data from the transaction table to the SALES table, every month.

You can use the following INSERT INTO and TRUNCATE commands to accomplish the task.

insert into sales (select * from sales_monthly);
truncate sales_monthly;

However, you can perform the same operation much more efficiently by using an ALTER TABLE APPEND command.

First, query the PG_TABLE_DEF system catalog table to verify that both tables have the same columns with identical column attributes.

select trim(tablename) as table, "column", trim(type) as type,
encoding, distkey, sortkey, "notnull" 
from pg_table_def where tablename like 'sales%';

     table     |   column   |  type         | encoding | distkey | sortkey | notnull
---------------+------------+---------------+----------+---------+---------+---------    
 sales         | salesid    | integer       | delta32k | f       |       0 | t
 sales         | listid     | integer       | delta32k | t       |       0 | t
 sales         | sellerid   | integer       | delta32k | f       |       0 | t
 sales         | buyerid    | integer       | delta32k | f       |       0 | t
 sales         | eventid    | integer       | delta32k | f       |       0 | t
 sales         | dateid     | smallint      | delta    | f       |       1 | t
 sales         | qtysold    | smallint      | delta    | f       |       0 | t
 sales         | pricepaid  | numeric(8,2)  | mostly32 | f       |       0 | f
 sales         | commission | numeric(8,2)  | delta32k | f       |       0 | f
 sales         | saletime   | timestamp     | none     | f       |       0 | f
 sales_month   | salesid    | integer       | delta32k | f       |       0 | t
 sales_month   | listid     | integer       | delta32k | t       |       0 | t
 sales_month   | sellerid   | integer       | delta32k | f       |       0 | t
 sales_month   | buyerid    | integer       | delta32k | f       |       0 | t
 sales_month   | eventid    | integer       | delta32k | f       |       0 | t
 sales_month   | dateid     | smallint      | delta    | f       |       1 | t
 sales_month   | qtysold    | smallint      | delta    | f       |       0 | t
 sales_month   | pricepaid  | numeric(8,2)  | mostly32 | f       |       0 | f
 sales_month   | commission | numeric(8,2)  | delta32k | f       |       0 | f
 sales_month   | saletime   | timestamp     | none     | f       |       0 | f

Next, look at the size of each table.

select count(*) from sales_monthly;
 count
-------
  2000
(1 row)

select count(*) from sales;
 count
-------
 412,214
(1 row)

Now execute the following ALTER TABLE APPEND command.

alter table sales append from sales_monthly;         

Look at the size of each table again. The SALES_MONTHLY table now has 0 rows, and the SALES table has grown by 2000 rows.

select count(*) from sales_monthly;
 count
-------
     0
(1 row)

select count(*) from sales;
 count
-------
 414214
(1 row)

If the source table has more columns than the target table, specify the IGNOREEXTRA parameter. The following example uses the IGNOREEXTRA parameter to ignore extra columns in the SALES_LISTING table when appending to the SALES table.

alter table sales append from sales_listing ignoreextra;

If the target table has more columns than the source table, specify the FILLTARGET parameter. The following example uses the FILLTARGET parameter to populate columns in the SALES_REPORT table that don't exist in the SALES_MONTH table.

alter table sales_report append from sales_month filltarget;