Redshift CREATE, ALTER, DROP, RENAME Database Commands and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:Redshift
  • Reading time:3 mins read

You can use Redshift PostgreSQL to create, drop, rename and change the database owners. Below given some important commands that you should know if you are working as a Redshift database administrator. In this article, we will check Redshift create, drop, rename database commands and some of examples.

Read:

Netezza Create Database Command

You can use CREATE DATABASE command to create new database in Redshift cluster:

Redshift CREATE DATABASE Syntax:

Below is the syntax to create database in Redshift cluste:

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

Where CONNECTION LIMIT indicates the maximum number of database connections users are permitted to have open concurrently

Redshift CREATE DATABASE Examples

Below is the example to create database in Redshift:

training=# create database testDb with owner vithals; 
CREATE DATABASE

You should be an administrator, super user or have CREATE DATABASE privileges to use this command.

Database names can be up to a maximum length of 127 bytes, and should not be any RESERVED keywords.

Note that, you can create maximum 60 user-defined database per Redshift cluster.

Redshift RENAME Database Command

Below is command and example to rename Redshift database:

ALTER DATABASE database_name RENAME TO new_name ;

training=# alter database testDb rename to newtestDb; 
ALTER DATABASE

Note that, you cannot rename the database that you are connect to.

The data and type remains same after rename the database.

You should recompile all the view those are associated with the old database to point to renamed database.

Redshift Change Ownership of Database

You can use ALTER DATABASE command and example to change the database owner in Redshift cluster:

ALTER DATABASE database_name OWNER TO new_owner ;

training=# alter database newtestDb owner to newtestuser; 
ALTER DATABASE

The only prerequisites here is that, the new owner must be a current used of the system. Create the user first if not exists.

Redshift Change CONNECTION LIMIT of Database

You can use ALTER DATABASE command and example to change CONNECTION LIMIT to the database in Redshift cluster:

ALTER DATABASE database_name CONNECTION LIMIT new_limit;

training=# alter database newtestDb connection limit 100; 
ALTER DATABASE

Redshift Drop Database Command

You can use DROP DATABASE command and example to drop database in Redshift cluster:

DROP DATABASE database_name;

training=# drop database newtestDb; 
DROP DATABASE

Dropping the Redshift database removes the entries for an existing database and deletes the directory that contains the data. Entire data and objects will be removed from the Redshift cluster.

Tags: