CREATE DATABASE
创建新数据库。
您不能在以下事务块中运行 CREATE DATABASE:(BEGIN ... END)。有关事务的更多信息,请参阅 可序列化的隔离。
语法
CREATE DATABASE database_name [ WITH ] [ OWNER [=] db_owner ] [ CONNECTION LIMIT { limit | UNLIMITED } ] [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ] [ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
参数
- database_name
-
新数据库的名称。有关有效名称的更多信息,请参阅名称和标识符。
- WITH
-
可选关键字。
- OWNER
-
指定数据库所有者。
- =
-
可选字符。
- db_owner
-
数据库所有者的用户名。
- CONNECTION LIMIT { limit | UNLIMITED }
-
允许用户同时打开的数据库连接的最大数量。此限制不适用于超级用户。使用 UNLIMITED 关键字设置允许的并行连接的最大数量。可能对每个用户的连接数量也会施加限制。有关更多信息,请参阅CREATE USER。默认为 UNLIMITED。要查看当前连接,请查询 STV_SESSIONS 系统视图。
注意 如果用户及数据库连接限制均适用,当用户尝试连接时,必须有一个同时低于这两个限制的未使用的连接槽可用。
- COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE }
-
指定字符串搜索或比较是 CASE_SENSITIVE 还是 CASE_INSENSITIVE 的子句。默认值为 CASE_SENSITIVE。
- ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT }
-
指定针对数据库运行查询时使用的隔离级别的子句。
SERIALIZABLE 隔离–为并发事务提供完全可序列性。这是在预置集群中创建的数据库的原定设置。有关更多信息,请参阅可序列化的隔离。
SNAPSHOT 隔离–提供隔离级别,以防止更新和删除冲突。这是在无服务器端点中创建的数据库的原定设置。
您可以按如下方式查看数据库运行的并发模型:
查询 STV_DB_ISOLATION_LEVEL 目录视图。有关更多信息,请参阅STV_DB_ISOLATION_LEVEL。
SELECT * FROM stv_db_isolation_level;
查询 PG_DATABASE_INFO 视图。
SELECT datname, datconfig FROM pg_database_info;
每个数据库的隔离级别出现在
concurrency_model
键旁边。值为1
时,表示 SNAPSHOT。值为2
时,表示 SERIALIZABLE。
在 Amazon Redshift 数据库中,SERIALIZABLE 和 SNAPSHOT 隔离都是可序列化隔离级别的类型。也就是说,根据 SQL 标准,防止脏读、不可重复读和幻读。这两种隔离级别都可保证某事务对该事务开始时就存在的数据快照进行操作,并且没有任何其他事务可以更改该快照。但是,SNAPSHOT 隔离不能提供完全可序列性,因为它不能防止在不同的表行上进行写入偏斜插入和更新。
以下场景说明了使用 SNAPSHOT 隔离级别的写入偏斜更新。名为
Numbers
的表包含名为digits
的列,其中包含0
和1
值。每个用户的 UPDATE 语句不会与其他用户重叠。但是,0
和1
值可交换。它们运行的 SQL 遵循以下时间表,结果如下:Time 用户 1 操作 用户 2 操作 1 BEGIN; 2 BEGIN; 3 SELECT * FROM Numbers; digits ------ 0 1
4 SELECT * FROM Numbers; digits ------ 0 1
5 UPDATE Numbers SET digits=0 WHERE digits=1; 6 SELECT * FROM Numbers; digits ------ 0 0
7 Update Numbers SET digits=1 WHERE digits=0; 8 SELECT * FROM Numbers; digits ------ 1 1
9 COMMIT; 10 COMMIT; 11 SELECT * FROM Numbers; digits ------ 1 0
如果使用可序列化的隔离运行同一场景,则 Amazon Redshift 因可序列化违规而终止用户 2 并返回错误
1023
。有关更多信息,请参阅如何修复可序列化的隔离错误。在这种情况下,只有用户 1 可以成功提交。并非所有工作负载都需要可序列化隔离作为一项要求,在这种情况下,快照隔离足以作为数据库的目标隔离级别。
将 CREATE DATABASE 与数据共享结合使用的语法
以下语法描述了用于从数据共享中创建数据库以在同一 Amazon 账户内共享数据的 CREATE DATABASE 命令。
CREATE DATABASE database_name FROM DATASHARE datashare_name OF [ ACCOUNT account_id ] NAMESPACE namespace_guid
以下语法描述了用于从数据共享中创建数据库以在 Amazon 账户间共享数据的 CREATE DATABASE 命令。
CREATE DATABASE database_name FROM DATASHARE datashare_name OF ACCOUNT account_id NAMESPACE namespace_guid
将 CREATE DATABASE 与数据共享结合使用的参数
- FROM DATASHARE
-
指示数据库所在位置的关键词。
- datashare_name
-
创建使用者数据库所在的数据共享的名称。
- NAMESPACE namespace_guid
-
指定数据共享所属的创建者命名空间的值。
- ACCOUNT account_id
-
指定数据共享所属的创建者账户的值。
用于数据共享的 CREATE DATABASE 的使用说明
作为使用者账户管理员,当您使用 CREATE DATABASE 从 Amazon 账户内的数据共享创建数据库时,指定 NAMESPACE 选项。ACCOUNT 选项为可选项。
当您使用 CREATE DATABASE 从 Amazon 账户间的数据共享创建数据库时,同时指定 ACCOUNT 和 NAMESPACE 选项。
对于使用者集群上的数据共享,一个数据共享仅可创建一个使用者数据库。不能创建多个引用同一数据共享的使用者数据库。
CREATE DATABASE 限制
Amazon Redshift 针对数据库强制实施以下限制:
-
每个集群最多 60 个用户定义的数据库。
-
数据库名称最多为 127 个字节。
-
数据库名称不能使用保留字。
数据库排序规则
排序规则是一组规则,用于定义数据库引擎如何对 SQL 中的字符类型数据进行比较和排序。不区分大小写的排序规则是最常用的排序规则。Amazon Redshift 使用不区分大小写的排序规则以帮助从其他数据仓库系统迁移。凭借对不区分大小写的排序规则的本机支持,Amazon Redshift 继续使用重要的调整或优化方法,如分配键、排序键或范围限制扫描。
COLLATE 子句指定数据库中所有 CHAR 和 VARCHAR 列的默认排序规则。如果指定了 CASE_INSENSITIVE,则所有 CHAR 或 VARCHAR 列都使用不区分大小写的排序规则。有关排序规则的信息,请参阅排序规则序列。
在不区分大小写的列中插入或摄取的数据将保持其原始大小写。但是所有基于比较的字符串操作,包括排序和分组都不区分大小写。模式匹配操作(如类似于的 LIKE 谓词)和正则表达式函数也不区分大小写。
以下 SQL 操作支持适用的排序规则语义:
比较运算符:=、<>、<、<=、>、>=。
LIKE 运算符
ORDER BY 子句
GROUP BY 子句
使用字符串比较的聚合函数,例如 MIN、MAX 和 LISTAGG
窗口函数,如 PARTITION BY 子句和 ORDER BY 子句
标量函数 greatest() 和 least()、STRPOS()、REGEXP_COUNT()、REGEXP_REPLACE()、REGEXP_INSTR()、REGEXP_SUBSTR()
Distinct 子句
UNION、INTERSECT 和 EXCEPT
IN LIST
对于外部查询(包括 Amazon Redshift Spectrum 和 Aurora PostgreSQL 联合查询),VARCHAR 或 CHAR 列的排序规则与当前数据库级别的排序规则相同。
以下示例将查询 Amazon Redshift Spectrum 表:
SELECT ci_varchar FROM spectrum.test_collation WHERE ci_varchar = 'AMAZON'; ci_varchar ---------- amazon Amazon AMAZON AmaZon (4 rows)
有关如何使用数据库排序规则创建表的信息,请参阅CREATE TABLE。
有关 COLLATE 函数的信息,请参阅COLLATE 函数。
数据库排序规则限制
以下是在 Amazon Redshift 中使用数据库排序规则时的限制:
所有系统表或视图(包括 PG 目录表和 Amazon Redshift 系统表)均区分大小写。
当使用者数据库和生产者数据库具有不同的数据库级排序规则时,Amazon Redshift 不支持跨数据库和跨集群查询。
Amazon Redshift 不支持仅领导节点查询中不区分大小写的排序规则。
以下示例显示了一个不受支持的不区分大小写的查询以及 Amazon Redshift 发送的错误:
SELECT collate(usename, 'case_insensitive') FROM pg_user; ERROR: Case insensitive collation is not supported in leader node only query.
Amazon Redshift 不支持区分大小写和不区分大小写的列之间的交互,例如比较、函数、联接或集合运算。
以下示例显示了区分大小写和不区分大小写的列交互时出现的错误:
CREATE TABLE test (ci_col varchar(10) COLLATE case_insensitive, cs_col varchar(10) COLLATE case_sensitive, cint int, cbigint bigint);
SELECT ci_col = cs_col FROM test; ERROR: Query with different collations is not supported yet.
SELECT concat(ci_col, cs_col) FROM test; ERROR: Query with different collations is not supported yet.
SELECT ci_col FROM test UNION SELECT cs_col FROM test; ERROR: Query with different collations is not supported yet.
SELECT * FROM test a, test b WHERE a.ci_col = b.cs_col; ERROR: Query with different collations is not supported yet.
Select Coalesce(ci_col, cs_col) from test; ERROR: Query with different collations is not supported yet.
Select case when cint > 0 then ci_col else cs_col end from test; ERROR: Query with different collations is not supported yet.
Amazon Redshift 不支持 SUPER 数据类型的排序规则。不支持在不区分大小写的数据库中创建 SUPER 列以及 SUPER 列和不区分大小写的列之间的交互。
以下示例在不区分大小写的数据库中创建一个以 SUPER 作为数据类型的表:
CREATE TABLE super_table (a super); ERROR: SUPER column is not supported in case insensitive database.
以下示例使用与 SUPER 数据进行比较的不区分大小写的字符串查询数据:
CREATE TABLE test_super_collation (s super, c varchar(10) COLLATE case_insensitive, i int);
SELECT s = c FROM test_super_collation; ERROR: Coercing from case insensitive string to SUPER is not supported.
要使这些查询起作用,请使用 COLLATE 函数将一个列的排序规则转换为与另一列匹配。有关更多信息,请参阅COLLATE 函数。
示例
创建数据库
以下示例创建名为 TICKIT 的数据库并将所有权授予用户 DWUSER。
create database tickit with owner dwuser;
要查看有关数据库的详细信息,请查询 PG_DATABASE_INFO 目录表。
select datname, datdba, datconnlimit from pg_database_info where datdba > 1; datname | datdba | datconnlimit -------------+--------+------------- admin | 100 | UNLIMITED reports | 100 | 100 tickit | 100 | 100
以下示例使用 SNAPSHOT 隔离级别创建名为 sampledb
的数据库。
CREATE DATABASE sampledb ISOLATION LEVEL SNAPSHOT;
以下示例从数据共享 salesshare 中创建了数据库 sales_db。
CREATE DATABASE sales_db FROM DATASHARE salesshare OF NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';
数据库排序规则示例
创建不区分大小写的数据库
以下示例将创建 sampledb
数据库、创建 T1
表,并将数据插入 T1
表中。
create database sampledb collate case_insensitive;
\connect sampledb;
CREATE TABLE T1 ( col1 Varchar(20) distkey sortkey );
INSERT INTO T1 VALUES ('bob'), ('john'), ('Mary'), ('JOHN'), ('Bob');
然后,查询查找含有 John
的结果。
SELECT * FROM T1 WHERE col1 = 'John'; col1 ------ john JOHN (2 row)
按不区分大小写的顺序排序
以下示例显示了表 T1 中不区分大小写的排序。Bob 与 bob 或 John 与 john 的排序具有不确定性,因为它们在不区分大小写的列中是相等的。
SELECT * FROM T1 ORDER BY 1; col1 ------ bob Bob JOHN john Mary (5 rows)
同样,以下示例显示了 GROUP BY 子句中不区分大小写的排序。Bob 和 bob 是相等的,并且属于同一个组。结果中显示哪一个是不确定的。
SELECT col1, count(*) FROM T1 GROUP BY 1; col1 | count -----+------ Mary | 1 bob | 2 JOHN | 2 (3 rows)
使用窗口函数对不区分大小写的列进行查询
以下示例在不区分大小写的列上查询窗口函数。
SELECT col1, rank() over (ORDER BY col1) FROM T1; col1 | rank -----+------ bob | 1 Bob | 1 john | 3 JOHN | 3 Mary | 5 (5 rows)
使用 DISTINCT 关键词进行查询
以下示例查询带有 DISTINCT 关键词的 T1
表。
SELECT DISTINCT col1 FROM T1; col1 ------ bob Mary john (3 rows)
使用 UNION 子句进行查询
以下示例显示来自表 T1
和 T2
的 UNION 的结果。
CREATE TABLE T2 AS SELECT * FROM T1;
SELECT col1 FROM T1 UNION SELECT col1 FROM T2; col1 ------ john bob Mary (3 rows)