Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # INSERT **Topics** + [Syntax](#r_INSERT_30-synopsis) + [Parameters](#r_INSERT_30-parameters) + [Usage notes](#r_INSERT_30_usage_notes) + [INSERT examples](c_Examples_of_INSERT_30.md) Inserts new rows into a table. You can insert a single row with the VALUES syntax, multiple rows with the VALUES syntax, or one or more rows defined by the results of a query (INSERT INTO...SELECT). **Note** We strongly encourage you to use the [COPY](r_COPY.md) command to load large amounts of data. Using individual INSERT statements to populate a table might be prohibitively slow. Alternatively, if your data already exists in other Amazon Redshift database tables, use INSERT INTO SELECT or [CREATE TABLE AS](r_CREATE_TABLE_AS.md) to improve performance. For more information about using the COPY command to load tables, see [Loading data in Amazon Redshift](t_Loading_data.md). **Note** The maximum size for a single SQL statement is 16 MB. ## Syntax ``` INSERT INTO table_name [ ( column [, ...] ) ] {DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ( { expression | DEFAULT } [, ...] ) [, ...] ] | query } ``` ## Parameters *table\$1name* A temporary or persistent table. Only the owner of the table or a user with INSERT privilege on the table can insert rows. If you use the *query* clause to insert rows, you must have SELECT privilege on the tables named in the query. Use INSERT (external table) to insert results of a SELECT query into existing tables on external catalog. For more information, see [INSERT (external table)](r_INSERT_external_table.md). *column* You can insert values into one or more columns of the table. You can list the target column names in any order. If you don't specify a column list, the values to be inserted must correspond to the table columns in the order in which they were declared in the CREATE TABLE statement. If the number of values to be inserted is less than the number of columns in the table, the first *n* columns are loaded. Either the declared default value or a null value is loaded into any column that isn't listed (implicitly or explicitly) in the INSERT statement. DEFAULT VALUES If the columns in the table were assigned default values when the table was created, use these keywords to insert a row that consists entirely of default values. If any of the columns don't have default values, nulls are inserted into those columns. If any of the columns are declared NOT NULL, the INSERT statement returns an error. VALUES Use this keyword to insert one or more rows, each row consisting of one or more values. The VALUES list for each row must align with the column list. To insert multiple rows, use a comma delimiter between each list of expressions. Do not repeat the VALUES keyword. All VALUES lists for a multiple-row INSERT statement must contain the same number of values. *expression* A single value or an expression that evaluates to a single value. Each value must be compatible with the data type of the column where it is being inserted. If possible, a value whose data type doesn't match the column's declared data type is automatically converted to a compatible data type. For example: + A decimal value `1.1` is inserted into an INT column as `1`. + A decimal value `100.8976` is inserted into a DEC(5,2) column as `100.90`. You can explicitly convert a value to a compatible data type by including type cast syntax in the expression. For example, if column COL1 in table T1 is a CHAR(3) column: ``` insert into t1(col1) values('Incomplete'::char(3)); ``` This statement inserts the value `Inc` into the column. For a single-row INSERT VALUES statement, you can use a scalar subquery as an expression. The result of the subquery is inserted into the appropriate column. Subqueries aren't supported as expressions for multiple-row INSERT VALUES statements. DEFAULT Use this keyword to insert the default value for a column, as defined when the table was created. If no default value exists for a column, a null is inserted. You can't insert a default value into a column that has a NOT NULL constraint if that column doesn't have an explicit default value assigned to it in the CREATE TABLE statement. *query* Insert one or more rows into the table by defining any query. All of the rows that the query produces are inserted into the table. The query must return a column list that is compatible with the columns in the table, but the column names don't have to match. ## Usage notes **Note** We strongly encourage you to use the [COPY](r_COPY.md) command to load large amounts of data. Using individual INSERT statements to populate a table might be prohibitively slow. Alternatively, if your data already exists in other Amazon Redshift database tables, use INSERT INTO SELECT or [CREATE TABLE AS](r_CREATE_TABLE_AS.md) to improve performance. For more information about using the COPY command to load tables, see [Loading data in Amazon Redshift](t_Loading_data.md). The data format for the inserted values must match the data format specified by the CREATE TABLE definition. After inserting a large number of new rows into a table: + Vacuum the table to reclaim storage space and re-sort rows. + Analyze the table to update statistics for the query planner. When values are inserted into DECIMAL columns and they exceed the specified scale, the loaded values are rounded up as appropriate. For example, when a value of `20.259` is inserted into a DECIMAL(8,2) column, the value that is stored is `20.26`. You can insert into 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 [GENERATED BY DEFAULT AS IDENTITY](r_CREATE_TABLE_NEW.md#identity-generated-bydefault-clause). # INSERT examples The CATEGORY table in the TICKIT database contains the following rows: ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows) ``` Create a CATEGORY\$1STAGE table with a similar schema to the CATEGORY table but define default values for the columns: ``` create table category_stage (catid smallint default 0, catgroup varchar(10) default 'General', catname varchar(10) default 'General', catdesc varchar(50) default 'General'); ``` The following INSERT statement selects all of the rows from the CATEGORY table and inserts them into the CATEGORY\$1STAGE table. ``` insert into category_stage (select * from category); ``` The parentheses around the query are optional. This command inserts a new row into the CATEGORY\$1STAGE table with a value specified for each column in order: ``` insert into category_stage values (12, 'Concerts', 'Comedy', 'All stand-up comedy performances'); ``` You can also insert a new row that combines specific values and default values: ``` insert into category_stage values (13, 'Concerts', 'Other', default); ``` Run the following query to return the inserted rows: ``` select * from category_stage where catid in(12,13) order by 1; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand-up comedy performances 13 | Concerts | Other | General (2 rows) ``` The following examples show some multiple-row INSERT VALUES statements. The first example inserts specific CATID values for two rows and default values for the other columns in both rows. ``` insert into category_stage values (14, default, default, default), (15, default, default, default); select * from category_stage where catid in(14,15) order by 1; catid | catgroup | catname | catdesc -------+----------+---------+--------- 14 | General | General | General 15 | General | General | General (2 rows) ``` The next example inserts three rows with various combinations of specific and default values: ``` insert into category_stage values (default, default, default, default), (20, default, 'Country', default), (21, 'Concerts', 'Rock', default); select * from category_stage where catid in(0,20,21) order by 1; catid | catgroup | catname | catdesc -------+----------+---------+--------- 0 | General | General | General 20 | General | Country | General 21 | Concerts | Rock | General (3 rows) ``` The first set of VALUES in this example produces the same results as specifying DEFAULT VALUES for a single-row INSERT statement. The following examples show INSERT behavior when a table has an IDENTITY column. First, create a new version of the CATEGORY table, then insert rows into it from CATEGORY: ``` create table category_ident (catid int identity not null, catgroup varchar(10) default 'General', catname varchar(10) default 'General', catdesc varchar(50) default 'General'); insert into category_ident(catgroup,catname,catdesc) select catgroup,catname,catdesc from category; ``` Note that you can't insert specific integer values into the CATID IDENTITY column. IDENTITY column values are automatically generated. The following example demonstrates that subqueries can't be used as expressions in multiple-row INSERT VALUES statements: ``` insert into category(catid) values ((select max(catid)+1 from category)), ((select max(catid)+2 from category)); ERROR: can't use subqueries in multi-row VALUES ``` The following example shows an insert into a temporary table populated with data from the `venue` table using the `WITH SELECT` clause. For more information about the `venue` table, see [Sample database](c_sampledb.md). First, create the temporary table `#venuetemp`. ``` CREATE TABLE #venuetemp AS SELECT * FROM venue; ``` List the rows in the `#venuetemp` table. ``` SELECT * FROM #venuetemp ORDER BY venueid; venueid | venuename | venuecity | venuestate| venueseats --------+--------------------------+------------+-----------+------------ 1 Toyota Park Bridgeview IL 0 2 Columbus Crew Stadium Columbus OH 0 3 RFK Stadium Washington DC 0 4 CommunityAmerica Ballpark Kansas City KS 0 5 Gillette Stadium Foxborough MA 68756 ... ``` Insert 10 duplicate rows in the `#venuetemp` table using the `WITH SELECT` clause. ``` INSERT INTO #venuetemp (WITH venuecopy AS (SELECT * FROM venue) SELECT * FROM venuecopy ORDER BY 1 LIMIT 10); ``` List the rows in the `#venuetemp` table. ``` SELECT * FROM #venuetemp ORDER BY venueid; venueid | venuename | venuecity | venuestate| venueseats --------+--------------------------+------------+-----------+------------ 1 Toyota Park Bridgeview IL 0 1 Toyota Park Bridgeview IL 0 2 Columbus Crew Stadium Columbus OH 0 2 Columbus Crew Stadium Columbus OH 0 3 RFK Stadium Washington DC 0 3 RFK Stadium Washington DC 0 4 CommunityAmerica Ballpark Kansas City KS 0 4 CommunityAmerica Ballpark Kansas City KS 0 5 Gillette Stadium Foxborough MA 68756 5 Gillette Stadium Foxborough MA 68756 ... ```