ALTER TABLE APPEND - Amazon Redshift

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.

Note

ALTER TABLE APPEND moves data blocks between the source table and the target table. To improve performance, ALTER TABLE APPEND doesn't compact storage as part of the append operation. As a result, storage usage increases temporarily. To reclaim the space, run a VACUUM operation.

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 can't 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 IGNOREEXTRA parameter. For more information, see ALTER TABLE APPEND usage notes.

You can append a GENERATED BY DEFAULT AS IDENTITY column. You can update columns defined as GENERATED BY DEFAULT AS IDENTITY with values that you supply. For more information, see ALTER TABLE APPEND usage notes.

The target table must be a permanent table. However, the source can be a permanent table or a materialized view configured for streaming ingestion. Both objects must use the same distribution style and distribution key, if one is defined. If the objects are sorted, both objects 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 can't be rolled back. You can't run ALTER TABLE APPEND within a transaction block (BEGIN ... END). For more information about transactions, see Serializable isolation.

Required privileges

Depending on the ALTER TABLE APPEND command, one of the following privileges is required:

  • Superuser

  • Users with the ALTER TABLE system privilege

  • Users with DELETE and SELECT privileges on the source table, and INSERT privilege on the target table

Syntax

ALTER TABLE target_table_name APPEND FROM [ source_table_name | source_materialized_view_name ] [ IGNOREEXTRA | FILLTARGET ]

Appending from a materialized view works only in the case where your materialized view is configured for Streaming ingestion.

Parameters

target_table_name

The name of the table to which rows are 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.

FROM source_materialized_view_name

The name of a materialized view that provides the rows to be appended. Appending from a materialized view works only in the case where your materialized view is configured for Streaming ingestion. The source materialized view must already exist.

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 can't 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 can't 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 table 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 target 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 can't 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 can't 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.

You can append a default identity column with the ALTER TABLE APPEND statement. For more information, see CREATE TABLE.

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 | lzo | false | 0 | true sales | listid | integer | none | true | 1 | true sales | sellerid | integer | none | false | 2 | true sales | buyerid | integer | lzo | false | 0 | true sales | eventid | integer | mostly16 | false | 0 | true sales | dateid | smallint | lzo | false | 0 | true sales | qtysold | smallint | mostly8 | false | 0 | true sales | pricepaid | numeric(8,2) | delta32k | false | 0 | false sales | commission | numeric(8,2) | delta32k | false | 0 | false sales | saletime | timestamp without time zone | lzo | false | 0 | false salesmonth | salesid | integer | lzo | false | 0 | true salesmonth | listid | integer | none | true | 1 | true salesmonth | sellerid | integer | none | false | 2 | true salesmonth | buyerid | integer | lzo | false | 0 | true salesmonth | eventid | integer | mostly16 | false | 0 | true salesmonth | dateid | smallint | lzo | false | 0 | true salesmonth | qtysold | smallint | mostly8 | false | 0 | true salesmonth | pricepaid | numeric(8,2) | delta32k | false | 0 | false salesmonth | commission | numeric(8,2) | delta32k | false | 0 | false salesmonth | saletime | timestamp without time zone | lzo | false | 0 | false

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 run 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;

The following example shows an example of how to use ALTER TABLE APPEND with a materialized view as a source.

ALTER TABLE target_tbl APPEND FROM my_streaming_materialized_view;

The table and materialized view names in this example are samples. Appending from a materialized view works only in the case where your materialized view is configured for Streaming ingestion. It moves all records in the source materialized view to a target table with the same schema as the materialized view and leaves the materialized view intact. This is the same behavior as when the source of the data is a table.