从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 # INSERT 示例 TICKIT 数据库中的 CATEGORY 表包含以下行： ``` 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) ``` 使用与 CATEGORY 表类似的 schema 来创建 CATEGORY\$1STAGE 表，但为列定义默认值： ``` create table category_stage (catid smallint default 0, catgroup varchar(10) default 'General', catname varchar(10) default 'General', catdesc varchar(50) default 'General'); ``` 下面的 INSERT 语句从 CATEGORY 表中选择所有行并将它们插入 CATEGORY\$1STAGE 表。 ``` insert into category_stage (select * from category); ``` 查询两旁的括号是可选的。 此命令在 CATEGORY\$1STAGE 表中插入新行，并按顺序为每列指定值： ``` insert into category_stage values (12, 'Concerts', 'Comedy', 'All stand-up comedy performances'); ``` 您还可以插入结合使用特定值和默认值的新行： ``` insert into category_stage values (13, 'Concerts', 'Other', default); ``` 运行以下查询以返回插入的行： ``` 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) ``` 下面的示例说明了一些多行 INSERT VALUES 语句。第一个示例为两行插入特定的 CATID 值，并为这两行中的其他列插入默认值。 ``` 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) ``` 下一个示例插入包含特定值和默认值的各种组合的三行： ``` 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) ``` 本示例中的第一组 VALUES 生成的结果与为单行 INSERT 语句指定 DEFAULT VALUES 所生成的结果相同。 以下示例说明当表具有 IDENTITY 列时的 INSERT 行为。首先，创建 CATEGORY 表的新版本，然后将行从 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; ``` 请注意，您不能将特定的整数值插入到 CATID IDENTITY 列。IDENTITY 列值会自动生成。 以下示例说明了不能在多行 INSERT VALUES 语句中将子查询用作表达式： ``` 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 ``` 以下示例显示了在临时表中插入的内容，临时表中使用 `WITH SELECT` 子句填充了 `venue` 表中的数据。有关 `venue` 表的更多信息，请参阅 [示例数据库](c_sampledb.md)。 首先，创建临时表 `#venuetemp`。 ``` CREATE TABLE #venuetemp AS SELECT * FROM venue; ``` 列出 `#venuetemp` 表中的行。 ``` 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 ... ``` 使用 `WITH SELECT` 子句在 `#venuetemp` 表中插入 10 个重复的行。 ``` INSERT INTO #venuetemp (WITH venuecopy AS (SELECT * FROM venue) SELECT * FROM venuecopy ORDER BY 1 LIMIT 10); ``` 列出 `#venuetemp` 表中的行。 ``` 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 ... ```