SQL Data Definition Language for Database in PostgreSQL

As was described into my previous post SQL Commands Types cheat sheet Data Definition Language, or just DDL, is a set of commands for creating, deleting, renaming databases and tables. In this tutorial I will describe how to use DDL with databases(schemes). PostgreSQL will be used for demonstration.

How to setup PostgreSQL (and PgAdmin3) on Ubuntu please refer to this tutorial: How to install PostgreSQL on Ubuntu 16.04

CREATE database

CREATE DATABASE blog;

PostgreSQL will create a database named blog with default parameters. But we can create database with specifying one of the following parameter (or all of them):

OWNER - the user's role which will own the new database
TEMPLATE - template from which database should be created (template1 is used by default)
ENCODING - Character set encoding to use in the new database. 
TABLESPACE - tablespace's name to be associated with the new database. 
CONNECTION LIMIT - amount of concurrent connection allowed to the new database. -1 set by default (no limits)

To create database named blog with UTF8 encodding, owned by user blog_admin and with 5 allowed connections:

CREATE DATABASE blog OWNER = blog_admin ENCODING = UTF8 CONNECTION LIMIT = 5;

• SQL standard doesn’t have CREATE DATABASE statement.

• CREATE DATABASE can not be executed inside a transaction block.

Modify the database structure

ALTER command is used for changing one of the database’s attribute or for renaming database.

ALTER DATABASE blog CONNECTION LIMIT -1;

will set the unlimited connections to databaseblog.

ALTER DATABAE blog RENAME TO my_new_blog;

will rename the existing database blog to my_new_blog

ALTER command can be also used with the following commands for changing one of the database’s attributes:

CONNECTION LIMIT - changes the connection limit to the specified value
RENAME TO - renames the database to the specified name
OWNER TO - sets the database's owner to the specified one.
SET TABLESPACE - changes the tablespace to the specified value
SET - adds/changes a specified runtime parameter (e.g. search path, time zone, index scan, etc)
RESET - removes the specified runtime parameter (e.g. search path, time zone, index scan, etc)

Remove a database

DROP command is used for removing/deleting the database.

DROP DATABASE blog;

will remove the database named blog. In case when specified database doesn’t exist the following error will occur:

ERROR:  database "specified_database_name" does not exist

IF EXISTS parameter will prevent us from such errors.

DROP DATABASE IF EXISTS blog;

Specified database will be removed only if it exists, otherwise nothing will happen.