Syntax Parameters Syntax for use with a datashare Usage notes for CREATE DATABASE for data sharing CREATE DATABASE limits Database collation for CREATE DATABASE Examples

CREATE DATABASE

Creates a new database.

You can't run CREATE DATABASE within a transaction block (BEGIN ... END). For more information about transactions, see Serializable isolation.

Syntax


CREATE DATABASE database_name [ WITH ]
[ OWNER [=] db_owner ]
[ CONNECTION LIMIT { limit | UNLIMITED } ]
[ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE ]

Parameters

database_name: Name of the new database. For more information about valid names, see Names and identifiers.
WITH: Optional keyword.
OWNER: Specifies a database owner.
=: Optional character.
db_owner: Username for the database owner.
CONNECTION LIMIT { limit | UNLIMITED }: The maximum number of database connections users are permitted to have open concurrently. The limit isn't enforced for superusers. Use the UNLIMITED keyword to permit the maximum number of concurrent connections. A limit on the number of connections for each user might also apply. For more information, see CREATE USER. The default is UNLIMITED. To view current connections, query the STV_SESSIONS system view.

Note

If both user and database connection limits apply, an unused connection slot must be available that is within both limits when a user attempts to connect.
COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE: A clause that specifies whether string search or comparison is CASE_SENSITIVE or CASE_INSENSITIVE. The default is CASE_SENSITIVE.

Syntax for using CREATE DATABASE with a datashare

The following syntax describes the CREATE DATABASE command used to create databases from a datashare for sharing data within the same AWS account.


CREATE DATABASE database_name
FROM DATASHARE datashare_name OF [ ACCOUNT account_id ] NAMESPACE namespace_guid

The following syntax describes the CREATE DATABASE command used to create databases from a datashare for sharing data across AWS accounts.


CREATE DATABASE database_name
FROM DATASHARE datashare_name OF ACCOUNT account_id NAMESPACE namespace_guid

Parameters for using CREATE DATABASE with a datashare

FROM DATASHARE: A keyword that indicates where the datashare is located.
datashare_name: The name of the datashare that the consumer database is created on.
NAMESPACE namespace_guid: A value that specifies the producer namespace that the datashare belongs to.
ACCOUNT account_id: A value that specifies the producer account that the datashare belongs to.

Usage notes for CREATE DATABASE for data sharing

As a consumer account administrator, when you use CREATE DATABASE to create databases from datashares within the AWS account, specify the NAMESPACE option. The ACCOUNT option is optional.

When you use CREATE DATABASE to create databases from datashares across AWS accounts, specify both the ACCOUNT and NAMESPACE options.

You can create only one consumer database for one datashare on a consumer cluster. You can't create multiple consumer databases referring to the same datashare.

CREATE DATABASE limits

Amazon Redshift enforces these limits for databases:

Maximum of 60 user-defined databases per cluster.
Maximum of 127 bytes for a database name.
A database name can't be a reserved word.

Database collation

Collation is a set of rules that defines how database engine compares and sorts the character type data in SQL. Case-insensitive collation is the most commonly used collation. Amazon Redshift uses case-insensitive collation to facilitate migration from other data warehouse systems. With the native support of case-insensitive collation, Amazon Redshift continues to use important tuning or optimization methods, such as distribution keys, sort keys, or range restricted scan.

The COLLATE clause specifies the default collation for all CHAR and VARCHAR columns in the database. If CASE_INSENSITIVE is specified, all CHAR or VARCHAR columns use case-insensitive collation. For information about collation, see Collation sequences.

Data inserted or ingested in case-insensitive columns will keep its original case. But all comparison-based string operations including sorting and grouping are case-insensitive. Pattern matching operations such as LIKE predicates, similar to, and regular expression functions are also case-insensitive.

The following SQL operations support applicable collation semantics:

Comparison operators: =, <>, <, <=, >, >=.
LIKE operator
ORDER BY clauses
GROUP BY clauses
Aggregate functions that use string comparison, such as MIN and MAX and LISTAGG
Window functions, such as PARTITION BY clauses and ORDER BY clauses
Scalar functions greatest() and least(), STRPOS(), REGEXP_COUNT(), REGEXP_REPLACE(), REGEXP_INSTR(), REGEXP_SUBSTR()
Distinct clause
UNION, INTERSECT and EXCEPT
IN LIST

For external queries, including Amazon Redshift Spectrum and Aurora PostgreSQL federated queries, collation of VARCHAR or CHAR column is the same as the current database-level collation.

The following example queries a Amazon Redshift Spectrum table:


SELECT ci_varchar FROM spectrum.test_collation 
WHERE ci_varchar = 'AMAZON';

ci_varchar
----------
amazon
Amazon
AMAZON
AmaZon
(4 rows)

For information on how to create tables using database collation, see CREATE TABLE.

For information on the COLLATE function, see COLLATE function.

Database collation limitations

The following are limitations when working with database collation in Amazon Redshift:

All system tables or views, including PG catalog tables and Amazon Redshift system tables are case-sensitive.
When consumer database and producer database have different database-level collations, Amazon Redshift doesn't support cross-database and cross-cluster queries.
Amazon Redshift doesn't support case-insensitive collation in leader node-only query.

The following example shows an unsupported case-insensitive query and the error that Amazon Redshift sends:
```
SELECT collate(usename, 'case_insensitive') FROM pg_user;
ERROR:  Case insensitive collation is not supported in leader node only query.
```

Amazon Redshift doesn't support interaction between case-sensitive and case-insensitive columns, such as comparison, function, join, or set operations.

The following examples show errors when case-sensitive and case-insensitive columns interact:


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 doesn't support collation for SUPER data type. Creating SUPER columns in case-insensitive databases and interactions between SUPER and case-insensitive columns aren't supported.

The following example creates a table with the SUPER as the data type in the case-insensitive database:
```
CREATE TABLE super_table (a super);
ERROR: SUPER column is not supported in case insensitive database.
```
The following example queries data with a case-insensitive string comparing with the SUPER data:
```
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.
```

To make these queries work, use the COLLATE function to convert collation of one column to match the other. For more information, see COLLATE function.

Examples

Creating a database

The following example creates a database named TICKIT and gives ownership to the user DWUSER.


create database tickit
with owner dwuser;

To view details about databases, query the PG_DATABASE_INFO catalog table.


select datname, datdba, datconnlimit 
from pg_database_info
where datdba > 1;

 datname     | datdba | datconnlimit
-------------+--------+-------------
 admin       |    100 | UNLIMITED   
 reports     |    100 | 100         
 tickit      |    100 | 100

The following example creates the database Salesdb from the datashare SalesShare.


CREATE DATABASE Sales_db FROM DATASHARE SalesShare OF NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';

Database collation examples

Creating a case-insensitive database

The following example creates the sampledb database, creates the T1 table, and inserts data into the T1 table.


create database sampledb collate case_insensitive;


connect to sampledb;


CREATE TABLE T1 (
  col1 Varchar(20) distkey sortkey
);


INSERT INTO T1 VALUES ('bob'), ('john'), ('Mary'), ('JOHN'), ('Bob');

Then the query finds results with John.


SELECT * FROM T1 WHERE col1 = 'John';

 col1
 ------
 john
 JOHN
(2 row)

Ordering in a case-insensitive order

The following example shows the case-insensitive ordering with table T1. The ordering of Bob and bob or John and john is nondeterministic because they are equal in case-insensitive column.


SELECT * FROM T1 ORDER BY 1;

 col1
 ------
 bob
 Bob
 JOHN
 john
 Mary
(5 rows)

Similarly, the following example shows case-insensitive ordering with the GROUP BY clause. Bob and bob are equal and belong to the same group. It is nondeterministic which one shows up in the result.


SELECT col1, count(*) FROM T1 GROUP BY 1;

 col1 | count
 -----+------
 Mary |  1
 bob  |  2
 JOHN |  2
(3 rows)

Querying with a window function on case-insensitive columns

The following example queries a window function on a case-insensitive column.


SELECT col1, rank() over (ORDER BY col1) FROM T1;

 col1 | rank
 -----+------
 bob  |   1
 Bob  |   1
 john |   3
 JOHN |   3
 Mary |   5
(5 rows)

Querying with the DISTINCT keyword

The following example queries the T1 table with the DISTINCT keyword.


SELECT DISTINCT col1 FROM T1;
 
 col1
 ------
 bob
 Mary
 john
(3 rows)

Querying with the UNION clause

The following example shows the results from the UNION of the tables T1 and T2.


CREATE TABLE T2 AS SELECT * FROM T1;


SELECT col1 FROM T1 UNION SELECT col1 FROM T2;
 
 col1
 ------
 john 
 bob
 Mary
(3 rows)

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

COPY examples

CREATE DATASHARE